By: Jan Potgieter | Updated: 2024-01-22 | Comments | Related: > Application Development
Problem
In the first tip of this series (QR Code Generator in Web-Based App from data stored in SQL Server), we developed a web application designed to generate a unique QR code for every employee listed in our database. The second series tip (QR Code Generator Web Based App that uses SQL Server) walked through expanding the web application to scan the employees' QR codes as they arrive or leave the office.
As a result, the Attendance table maintains a one-to-many relationship among the employees and tracks their movements to and from their respective offices. This tip aims to show various types of data concerning employees' activities within the office premises. To do this, the database needs additional preparations and some SQL scripts.
Solution
As we continue enhancing our web application's capabilities, we have exciting developments on the horizon. Our objective is to expand the functionality to provide users with a richer set of data insights. Specifically, we will be adding features that enable us to:
- Display Arrival and Departure times for each employee on a per-date basis, offering a detailed view of their attendance history.
- Calculate and showcase the total "At Office" time for each employee, streamlining the tracking of their working hours.
- Offer a comprehensive list of employees and their "At Office" status at any specified date and time, facilitating real-time monitoring and accountability within the workplace.
These enhancements are poised to elevate the utility of our web application, making it an even more indispensable tool for effective employee management and attendance tracking.
Setting Up the Database Environment
Once the database is properly configured, building the application becomes a straightforward task.
The upcoming sections will guide you through the essential steps to prepare the database to seamlessly present the data in a user-friendly and understandable manner, ideal for management purposes.
You can utilize the conveniently provided SQL scripts to set up the database, tailored for seamless integration with the web application.
Step 1: Remove Old HRDatabase
Use this script to Drop the User in the database, the Login, and the Database.
-- MSSQLTips.com USE master; GO -- Drop the User ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbo] GO USE HRDatabase; GO DROP USER [HRDBUser] GO USE master; GO -- Sometimes the user is still logged. Kill the SPID of the logged in user to remove it -- sp_who2 -- KILL 66 -- Drop the Login DROP LOGIN [HRDBLogin]; GO USE master; GO -- Drop Database if it exist DROP DATABASE IF EXISTS HRDatabase; GO
Step 2: Create New HRDatabase
Use this script to Create the HRDatabase, the Login, and the User, and make the User the database owner.
-- MSSQLTips.com -- Create the New HRDatabase, Login and User USE master; GO -- Create the database if it doesn't exist CREATE DATABASE HRDatabase; GO -- Setup the Login and User that will be used with the Web Application USE master; GO -- Create the SQL Server Login: HRDBLogin CREATE LOGIN HRDBLogin WITH PASSWORD = N'MyP@ss3@1' --MUST_CHANGE , DEFAULT_DATABASE=HRDatabase , CHECK_EXPIRATION=ON , CHECK_POLICY=ON USE HRDatabase; GO -- Create the Database User: HRDBUser CREATE USER HRDBUser FOR LOGIN HRDBLogin GO -- Make the new User the Owner of the database ALTER ROLE db_owner ADD MEMBER HRDBUser GO
Step 3: Create Tables
Use this script to Create the following tables and their foreign keys:
- Companies
- Employees
- Title
- Gender
- LeaveTypes
- Addresses
- Leave
- Attendance
-- MSSQLTips.com
-- Create all the tables to be used in the database
USE HRDatabase;
GO
CREATE TABLE dbo.Companies (
id INT CONSTRAINT PK_Companies PRIMARY KEY IDENTITY (100,1),
CompanyName VARCHAR(80) NOT NULL,
CompAddress VARCHAR(80) NOT NULL,
CompContactNo VARCHAR(20) NOT NULL,
IsActive BIT CONSTRAINT DF_IsActive_Companies DEFAULT(1),
CreateDate DATETIME NOT NULL DEFAULT getdate()
);
CREATE TABLE dbo.Employees (
id INT CONSTRAINT PK_Employees PRIMARY KEY IDENTITY(100,1),
EmployeeName VARCHAR(80) NOT NULL,
ContactNo VARCHAR(20) NOT NULL,
Email VARCHAR(80) NOT NULL,
CompID INT NULL,
AddressID INT NULL,
TitleID INT NULL,
GenderID INT NULL,
QRCreated VARCHAR(3) NOT NULL CONSTRAINT DF_QRCreated_Employees DEFAULT ('No'),
IsActive BIT NULL CONSTRAINT DF_IsActive_Employees DEFAULT(1),
CreateDate DATETIME NOT NULL DEFAULT getdate()
);
CREATE TABLE dbo.Title (
id INT CONSTRAINT PK_Title PRIMARY KEY IDENTITY,
Title VARCHAR(80) NOT NULL,
TitleDescr VARCHAR(80) NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT getdate()
);
CREATE TABLE dbo.Gender (
id INT CONSTRAINT PK_Gender PRIMARY KEY IDENTITY,
Gender VARCHAR(80) NOT NULL,
GenderDescr VARCHAR(80) NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT getdate()
);
CREATE TABLE dbo.LeaveTypes (
id INT CONSTRAINT PK_LeaveTypes PRIMARY KEY IDENTITY,
LeaveType VARCHAR(80) NOT NULL,
LeaveDescr VARCHAR(80) NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT getdate()
);
CREATE TABLE dbo.Addresses (
id INT CONSTRAINT PK_Addresses PRIMARY KEY IDENTITY,
[Address] VARCHAR(256) NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT getdate()
);
CREATE TABLE dbo.Leave (
id INT CONSTRAINT PK_Leave PRIMARY KEY IDENTITY,
LeaveTypeID INTEGER NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NOT NULL,
LeaveNotes VARCHAR(800) NOT NULL,
EmpID INT NOT NULL,
CreateDate DATETIME NOT NULL DEFAULT getdate()
);
CREATE TABLE dbo.Attendance (
id INT CONSTRAINT Pk_Attendance PRIMARY KEY IDENTITY,
EmpID INT NOT NULL,
ArrDepWork VARCHAR(20) NOT NULL DEFAULT('Arrive'),
CreatedDate DATETIME NOT NULL CONSTRAINT [DF_CreatedDate_Scans] DEFAULT (getdate())
);
GO
/****************************************************************************************************/
-- Add the foreign keys that creates the links between the respective tables
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Companies
FOREIGN KEY(CompID) REFERENCES Companies(id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Addresses
FOREIGN KEY(AddressID) REFERENCES Addresses(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Titles
FOREIGN KEY(TitleID) REFERENCES Title(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE dbo.Employees ADD CONSTRAINT FK_Employees_Genders
FOREIGN KEY(GenderID) REFERENCES Gender(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE dbo.Leave ADD CONSTRAINT FK_Leave_Employees
FOREIGN KEY(EmpID) REFERENCES Employees(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE dbo.Leave ADD CONSTRAINT FK_Leave_LeaveTypes
FOREIGN KEY(LeaveTypeID) REFERENCES LeaveTypes(id) ON DELETE NO ACTION ON UPDATE NO ACTION;
ALTER TABLE dbo.Attendance ADD CONSTRAINT FK_Attendance_Employee
FOREIGN KEY (EmpID) REFERENCES dbo.[Employees] (id) ON DELETE CASCADE ON UPDATE CASCADE
USE master;
GO
Step 4: Insert Dummy Data
Use this script to insert dummy data for the above tables and a single record for an employee going on leave.
-- MSSQLTips.com
-- Insert dummy data to all the tables
USE HRDatabase;
GO
INSERT INTO dbo.Companies (CompanyName, CompAddress, CompContactNo) VALUES
('Elit Pharetra Ut LLP' , '576-5379 Eu St, Gwadar, 6269' , '582 593 5311'),
('Aenean Incorporated' , '434-4279 Adipiscing, Rd, Murdochville, 687' , '462 465 8259'),
('Odio Nam PC' , '672-2415 Mauris Av, Puntarenas, 216678' , '104 263 3671'),
('Luctus Et Inc' , '540-6232 Leo Road, Ratlam, 557662' , '408 566 8546'),
('Leo Incorporated' , '838-9516 Ut Street, Guarapuava, 545721' , '800 772 9022'),
('Donec Company' , '679-3154 Sapien St, Nova Kakhovka, 7845' , '348 984 4306'),
('Turpis LLC' , '795-1817 Vitae, St, Stargard Szczecinski, 431', '478 294 8631'),
('Mauris Incorporated' , '946-6131 Magna Avenue, Hospet, 45062' , '860 335 3458'),
('Ac Consulting' , '168-8242 Massa Av, Harstad, 88-37' , '984 737 1838'),
('Justo Corporation' , '843 Fusce Street, Christchurch, 58836' , '513 215 1519')
SELECT * FROM dbo.Companies
INSERT INTO dbo.Title (Title, TitleDescr) VALUES
('Mr' , 'Mister'),
('Mrs' , 'Mrs'),
('Miss' , 'Misses')
SELECT * FROM Title
INSERT INTO dbo.Gender (Gender, GenderDescr) VALUES
('Male' , 'Male'),
('Female' , 'Female')
SELECT * FROM dbo.Gender
INSERT INTO dbo.LeaveTypes (LeaveType, LeaveDescr) VALUES
('Annual' , 'Annual Leave'),
('Study' , 'Study Leave'),
('Sick' , 'Sick Leave'),
('Unpaid' , 'Unpaid Leave')
SELECT * FROM dbo.LeaveTypes
INSERT INTO dbo.Addresses (Address) VALUES
('123 North Street, Garsfontein, Pretoria') ,
('456 South Street, Brooklyn, Pretoria'),
('987 West Street, Lynnwood, Pretoria'),
('258 East Street, The Meadows, Pretoria'),
('100 Amber Street, Hatfield, Pretoria')
SELECT * FROM dbo.Addresses
INSERT INTO dbo.Employees (EmployeeName, ContactNo, Email, CompID, AddressID, TitleID, GenderID) VALUES
('Brendan Hewitt', '338 325 7289', '[email protected]' , 105 ,1, 1, 1),
('Bethany Joseph', '449 751 1256', '[email protected]', 105 ,2, 2, 2),
('Hadley Coleman', '562 813 8455', '[email protected]' , 105 ,5, 3, 1),
('Darryl Dominguez', '172 884 1864', '[email protected]' , 107 ,4, 3, 2),
('Brock Dickerson', '788 654 4950', '[email protected]' , 103 ,3, 2, 1),
('George Kramer', '366 279 8756', '[email protected]' , 102 ,1, 2, 1),
('Zeph Doyle', '587 403 0422', '[email protected]' , 104 ,1, 1, 2),
('Gabriel Graham', '245 566 2219', '[email protected]' , 108 ,2, 2, 1),
('Arden Graves', '976 914 7081', '[email protected]' , 105 ,2, 3, 2),
('Veronica Charles', '897 642 6386', '[email protected]' , 106 ,3, 3, 2),
('Amy Steele', '564 838 1446', '[email protected]' , 108 ,2, 2, 1),
('Hashim Howe', '581 251 0163', '[email protected]' , 102 ,1, 2, 1),
('Kevin Ortega', '725 555 3112', '[email protected]' , 102 ,5, 1, 2)
SELECT * FROM dbo.Employees
/****************************************************************************************************/
-- Add some leave for employee Jane Doe
-- Choose an appropriate date if you want
INSERT INTO dbo.Leave (LeaveTypeID, StartDate, EndDate, LeaveNotes, EmpID) VALUES
(1,
CAST(DATEADD(WEEK, 3, DATEADD(DAY, -DATEPART(WEEKDAY, GETDATE()) + 2, CAST(GETDATE() AS DATE)))AS DATE)
, CAST(DATEADD(WEEK, 3, DATEADD(DAY, -DATEPART(WEEKDAY, GETDATE()) + 6, CAST(GETDATE() AS DATE)))AS DATE)
, 'Annual leave to enjoy vacation at the coast', 102)
SELECT * FROM dbo.Leave
USE master;
GO
Step 5: Create Stored Procedures
Use this script to Create the five stored procedures to use with the Companies and Employees tables. The five stored procedures to use are to perform the following operations:
- Get a list of Companies / Employees
- Get a single Company / Employee
- Insert a Company / Employee
- Update a Company / Employee
- Delete a Company / Employee
-- MSSQLTips.com
-- Drop the Stored Procedures if they exist and you need to create them again
USE [HRDatabase]
GO
DROP PROCEDURE dbo.[usp_GetCompanies]
DROP PROCEDURE dbo.[usp_GetCompany]
DROP PROCEDURE dbo.[usp_InsCompany]
DROP PROCEDURE dbo.[usp_UpdCompany]
DROP PROCEDURE dbo.[usp_DelCompany]
DROP PROCEDURE dbo.[usp_GetEmployees]
DROP PROCEDURE dbo.[usp_GetEmployee]
DROP PROCEDURE dbo.[usp_InsEmployee]
DROP PROCEDURE dbo.[usp_UpdEmployee]
DROP PROCEDURE dbo.[usp_DelEmployee]
DROP PROCEDURE dbo.[usp_InsQRScan]
*/
----------------------------------------------------------------------------
USE [HRDatabase];
GO
-- Stored Procedure to return a list of all the companies
CREATE OR ALTER PROCEDURE dbo.usp_GetCompanies
AS
BEGIN
SELECT ID
,CompanyName
,CompAddress
,CompContactNo
,CreateDate
FROM dbo.Companies
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_GetCompanies;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to return a single Company based on an ID
CREATE OR ALTER PROCEDURE dbo.usp_GetCompany
@ID int
AS
BEGIN
SELECT ID
,CompanyName
,CompAddress
,CompContactNo
,CreateDate
FROM dbo.Companies
WHERE ID = @ID
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_GetCompany @ID = 104;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to Insert a single Company
CREATE OR ALTER PROCEDURE dbo.usp_InsCompany
@CompanyName varchar(80),
@CompAddress varchar(80),
@CompContactNo varchar(20)
AS
BEGIN
INSERT INTO dbo.Companies
(CompanyName
,CompAddress
,CompContactNo
,CreateDate)
VALUES
(@CompanyName
,@CompAddress
,@CompContactNo
,getdate())
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_InsCompany
@CompanyName = 'Zulu-Yankee Company',
@CompAddress = '123 Some street, Somewhere Far away, Europe ext 10',
@CompContactNo = '999 852 7401';
go
SELECT * FROM dbo.Companies;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to Update a single Company
CREATE OR ALTER PROCEDURE dbo.usp_UpdCompany
@ID int = null,
@CompanyName varchar(80) = null,
@CompAddress varchar(80) = null,
@CompContactNo varchar(20) = null
AS
BEGIN
UPDATE dbo.Companies
SET CompanyName = ISNULL(@CompanyName , CompanyName )
,CompAddress = ISNULL(@CompAddress , CompAddress )
,CompContactNo = ISNULL(@CompContactNo , CompContactNo )
WHERE ID = @ID
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_UpdCompany
@ID = 106,
@CompanyName = 'Zulu-Yanke Company',
@CompAddress = null,
@CompContactNo = '555 852 7401'
select * from dbo.Companies;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to Delete a single Company based on its ID
CREATE OR ALTER PROCEDURE dbo.usp_DelCompany
@ID int
AS
BEGIN
DELETE FROM dbo.Companies
WHERE ID = @ID
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_DelCompany
@ID = 103;
go
select * from dbo.Companies;
go
*/
/**************************************************************************************
Create Stored Procedures for Employees table
*/
----------------------------------------------------------------------------
-- Stored Procedure to return a list of all the employees
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployees
AS
BEGIN
SELECT e.[ID]
,e.[EmployeeName]
,e.[ContactNo]
,e.[Email]
,e.[CompID]
,e.[CreateDate]
,c.[CompanyName]
,e.[QRCreated]
FROM dbo.[Employees] e
LEFT JOIN dbo.[Companies] c on c.ID = e.CompID
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_GetEmployees;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to return a single Employee based on an ID
CREATE OR ALTER PROCEDURE dbo.usp_GetEmployee
@ID int
AS
BEGIN
SELECT e.[ID]
,[EmployeeName]
,[ContactNo]
,[Email]
,[CompID]
,[QRCreated]
,e.[CreateDate]
,c.CompanyName
FROM dbo.[Employees] e
JOIN dbo.[Companies] c on c.id = e.CompID
WHERE e.ID = @ID
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_GetEmployee @ID = 101;
EXEC dbo.usp_GetEmployee @ID = 102;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to Insert a single Employee
CREATE OR ALTER PROCEDURE dbo.usp_InsEmployee
@EmployeeName varchar(80),
@ContactNo varchar(80),
@Email varchar(20),
@CompID int
AS
BEGIN
INSERT INTO dbo.[Employees]
([EmployeeName]
,[ContactNo]
,[Email]
,[CompID])
VALUES
(@EmployeeName
,@ContactNo
,@Email
,@CompID)
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.[usp_InsEmployee]
@EmployeeName = 'James Brown',
@ContactNo = '999 852 7401',
@Email = '[email protected]',
@CompID = 101;
go
SELECT * FROM dbo.Employees;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to Update a single Employee
CREATE OR ALTER PROCEDURE dbo.usp_UpdEmployee
@ID int = null,
@EmployeeName varchar(80) = null,
@ContactNo varchar(80) = null,
@Email varchar(20) = null,
@CompID int = null,
@QRCreated varchar(3) = null
AS
BEGIN
UPDATE dbo.[Employees]
SET EmployeeName = ISNULL(@EmployeeName , EmployeeName )
,ContactNo = ISNULL(@ContactNo , ContactNo )
,Email = ISNULL(@Email , Email )
,CompID = ISNULL(@CompID , CompID )
,QRCreated = ISNULL(@QRCreated , QRCreated )
WHERE ID = @ID
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_UpdEmployee
@ID = 106,
@EmployeeName = null,
@ContactNo = '777 852 7401',
@Email = '[email protected]',
@CompID = 102
SELECT * FROM dbo.Employees;
go
*/
----------------------------------------------------------------------------
-- Stored Procedure to Delete a single Employee based on its ID
CREATE OR ALTER PROCEDURE dbo.usp_DelEmployee
@ID int
AS
BEGIN
DELETE FROM dbo.[Employees]
WHERE ID = @ID
END;
GO
/* To run the Stored Procedure you would execute the following:
EXEC dbo.usp_DelEmployee
@ID = 107;
go
SELECT * FROM dbo.Employees;
go
*/
---------------------------------------------------------------------
-- Create Attendance Insert Stored Procedure
CREATE or ALTER PROCEDURE dbo.usp_InsQRScan
@EmpID int
AS
/*
exec dbo.usp_InsQRScan @EmpID=100
*/
BEGIN
DECLARE @ArrDep VARCHAR(20),
@In INT,
@Out INT,
@AttWork VARCHAR(3)
SET @In = (
SELECT COUNT(*)
FROM dbo.Attendance
WHERE EmpID = @EmpID
AND ArrDepWork = 'Arrive'
AND CAST(CreatedDate AS DATE) = CAST(getdate() as date)
)
SET @Out = (
SELECT COUNT(*)
FROM dbo.Attendance
WHERE EmpID = @EmpID
AND ArrDepWork = 'Depart'
AND CAST(CreatedDate AS DATE) = CAST(getdate() as date)
)
SELECT @AttWork = (CASE WHEN @In > @Out THEN 'Yes' ELSE 'No' END)
IF (SELECT @AttWork) = 'Yes'
BEGIN
INSERT dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
VALUES (@EmpID, 'Depart', getdate())
END
ELSE
BEGIN
INSERT dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
VALUES (@EmpID, 'Arrive', getdate())
END
END;
go
--SELECT * FROM [HRDatabase].[dbo].[Employees]
--SELECT * FROM [HRDatabase].[dbo].[Attendance]
USE master;
GO
Step 6: Insert Dummy Data for the Attendance Table
Our next step is to generate simulated data for the Attendance table, replicating real-life scenarios where employees check in and out of work.
The script is designed to generate a week's worth of data, and all you need to do is modify the start date by selecting any Monday from the past as your reference point.
Note: The script below uses the SQL random function RAND to generate some of the arrival and departure times. As a result, your example time totals may not match the screenprints in this tip.
-- MSSQLTips.com
-- Insert some dummy data for Attendance
USE HRDatabase;
GO
-- SELECT * FROM HRDatabase.dbo.Attendance
-- DELETE FROM HRDatabase.dbo.Attendance;
-- Set the initial date for a Monday
DECLARE @theDate date = '2023-10-02'
-- Insert the Arrive times for all the employees
BEGIN
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Arrive',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('07:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
-- Add some movement to Employees during the day
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate) VALUES
(101, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:35')),
(101, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 11:25')),
(103, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:00')),
(103, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 11:00')),
(103, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 13:00')),
(103, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 14:00'))
-- Insert the Depart times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Depart',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('16:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
END
-- Set the date for Tuesday
SET @theDate = DATEADD(DAY, 1, @theDate)
BEGIN
-- Insert the Arrive times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Arrive',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('07:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
-- Add some movement to Employees during the day
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate) VALUES
(105, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:45')),
(105, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 11:45')),
(108, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:20')),
(108, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 11:30')),
(108, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 13:30')),
(108, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 14:10'))
-- Insert the Depart times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Depart',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('16:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
END
-- Set the date for Wednesday
SET @theDate = DATEADD(DAY, 1, @theDate)
BEGIN
-- Insert the Arrive times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Arrive',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('07:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
-- Add some movement to Employees during the day
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate) VALUES
(111, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 09:35')),
(111, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 10:25')),
(102, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 09:30')),
(102, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 10:00')),
(102, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 14:20')),
(102, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 14:50'))
-- Insert the Depart times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Depart',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('16:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
END
-- Set the date for Thursday
SET @theDate = DATEADD(DAY, 1, @theDate)
BEGIN
-- Insert the Arrive times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Arrive',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('07:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
-- Add some movement to Employees during the day
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate) VALUES
(106, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:35')),
(106, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 11:25')),
(106, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 12:00')),
(106, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 13:00')),
(107, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 13:00')),
(107, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 14:00'))
-- Insert the Depart times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Depart',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('16:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
END
-- Set the date for Friday
SET @theDate = DATEADD(DAY, 1, @theDate)
BEGIN
-- Insert the Arrive times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Arrive',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('07:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
-- Add some movement to Employees during the day
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate) VALUES
(109, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 08:35')),
(109, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 09:25')),
(109, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:00')),
(109, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 12:00')),
(110, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 11:00')),
(110, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 12:20'))
-- Insert the Depart times for all the employees
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Depart',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('16:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
END
-- Check the Attendance table
SELECT * FROM dbo.Attendance
USE master;
GO
Step 7: Examine Scenarios Related to Employee Activities
Let's test a few scenarios using our dummy data.
List of All Attendances.
After you have used the above script to insert dummy data, you can perform a SELECT on the Employees table joined with the Attendance table to get a list of all the attendances, in this case, 150 records:
-- MSSQLTips.com USE HRDatabase; GO SELECT e.id, e.EmployeeName, e.ContactNo, e.Email, a.ArrDepWork, a.CreatedDate FROM dbo.Employees e JOIN dbo.Attendance a on a.EmpID = e.id
Check a Specific Employee's Attendance.
You can also use an employee's ID to check a specific employee's attendance for the week:
-- MSSQLTips.com SELECT e.id, e.EmployeeName, e.ContactNo, e.Email, a.ArrDepWork, a.CreatedDate FROM dbo.Employees e JOIN dbo.Attendance a on a.EmpID = e.id WHERE e.id in (101) ORDER BY a.id
Retrieve the Arrival and Departure Times of Each Employee on Specific Dates.
First, extract some data into a temp table by getting the Arrive and Depart times per employee per date:
-- MSSQLTips.com
-- Get the Arrive and Depart times per employee per date
IF OBJECT_ID('tempdb..#Tmp1')IS NOT NULL DROP TABLE #Tmp1;
BEGIN
WITH PairedRecords AS (
SELECT
a1.EmpID,
e.EmployeeName,
CONVERT(DATE, a1.CreatedDate) AS AttendanceDate,
a1.ArrDepWork,
a1.CreatedDate
FROM
dbo.Attendance a1
JOIN dbo.Employees e ON e.id = a1.EmpID
WHERE
a1.ArrDepWork = 'Arrive'
)
SELECT EmpID, EmployeeName, AttendanceDate, CreatedDate [Arrive],
(SELECT top 1 a2.CreatedDate [Depart]
FROM dbo.Attendance a2
WHERE a2.CreatedDate > PairedRecords.CreatedDate
AND a2.EmpID = PairedRecords.EmpID
AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate) [Depart]
,
DATEDIFF(MINUTE, CreatedDate, (
SELECT top 1 a2.CreatedDate [Depart]
FROM dbo.Attendance a2
WHERE a2.CreatedDate > PairedRecords.CreatedDate
AND a2.EmpID = PairedRecords.EmpID
AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate
)) TotalTime
INTO #Tmp1
FROM PairedRecords
ORDER BY 1,4;
END
SELECT * FROM #Tmp1
WHERE EmpID in (101)
ORDER BY EmpID, Arrive
In the above image, you can see that employee Bethany Joseph has two entries for Monday, 2023-10-02, because the employee arrived at 07:32 and departed at 10:35 and arrived again at 11:25 and departed at the end of the day at 16:02. The working time equal to 183 minutes plus 277 minutes for a total of 460 minutes for the day.
Retrieve the Arrive and Depart Times for a Day an Employee Went to Lunch
-- MSSQLTips.com
WITH CombinedRecords AS (
SELECT
EmpID,
EmployeeName,
AttendanceDate,
MIN(Arrive) AS FirstArrive,
MAX(Depart) AS LastDepart
FROM
#Tmp1
WHERE EmpID in (101)
GROUP BY
EmpID,
EmployeeName,
AttendanceDate
)
SELECT
EmpID,
EmployeeName,
AttendanceDate,
FirstArrive,
LastDepart
FROM
CombinedRecords;
In the above image, you can see the employee's consolidated arrival and departure times are only a single consolidated record for that Monday.
Calculate the Total Daily Hours Worked By Each Employee.
-- MSSQLTips.com
SELECT EmpID, EmployeeName, AttendanceDate
, CONVERT(VARCHAR(5), DATEADD(MINUTE, SUM(TotalTime), 0), 108) AS TotalTime
FROM #Tmp1
WHERE EmpID in (101)
GROUP BY EmpID, EmployeeName, AttendanceDate
In the above image, you can see the totals by date for the employee.
Create a List of Employees Present at the Office at a Specific Date and Time.
Let's say it is 11:00 AM on a Monday, and you want to see who is currently at the office.
We need to change the script generating the dummy data to only simulate the situation:
-- MSSQLTips.com
-- Insert some dummy data for Attendance for Monday
USE HRDatabase;
GO
DELETE FROM HRDatabase.dbo.Attendance;
-- Set the initial date for a Monday
DECLARE @theDate date = '2023-10-02'
-- Insert the Arrive times for all the employees
BEGIN
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate)
SELECT
EmpID,
'Arrive',
CAST(
CAST(CAST(@theDate AS DATE) AS VARCHAR(11)) + ' ' +
CAST(DATEADD(MINUTE, CAST(RAND(CHECKSUM(NEWID())) * 60 AS INT), CAST('07:00' AS TIME)) AS VARCHAR(5))
AS DATETIME)
FROM (
VALUES
(101), (102), (103), (104), (105), (106), (107), (108), (109), (110), (111), (112)
) AS EmployeeList(EmpID);
-- Add some movement to Employees during the day
INSERT INTO dbo.Attendance(EmpID, [ArrDepWork], CreatedDate) VALUES
(101, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:35')),
(103, 'Depart', convert(datetime, convert(varchar, @theDate) + ' 10:00')),
(103, 'Arrive', convert(datetime, convert(varchar, @theDate) + ' 10:50'))
END
The above script first removes all the data in the dbo.Attendance table, then it generates data for all employees to arrive at work. However, the data shows that two employees departed the office, and only one returned.
Now, we can check who is at the office at 11:00 AM when the staff meeting is scheduled to start:
-- MSSQLTips.com
IF OBJECT_ID('tempdb..#Tmp1') IS NOT NULL DROP TABLE #Tmp1;
BEGIN
WITH PairedRecords AS (
SELECT
a1.EmpID,
e.EmployeeName,
CONVERT(DATE, a1.CreatedDate) AS AttendanceDate,
a1.ArrDepWork,
a1.CreatedDate
FROM
dbo.Attendance a1
JOIN dbo.Employees e ON e.id = a1.EmpID
WHERE
a1.ArrDepWork = 'Arrive'
)
SELECT EmpID, EmployeeName, AttendanceDate, CreatedDate [Arrive],
(SELECT top 1 a2.CreatedDate [Depart]
FROM dbo.Attendance a2
WHERE a2.CreatedDate > PairedRecords.CreatedDate
AND a2.EmpID = PairedRecords.EmpID
AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate) [Depart]
,
DATEDIFF(MINUTE, CreatedDate, (
SELECT top 1 a2.CreatedDate [Depart]
FROM dbo.Attendance a2
WHERE a2.CreatedDate > PairedRecords.CreatedDate
AND a2.EmpID = PairedRecords.EmpID
AND CONVERT(DATE, a2.CreatedDate) = PairedRecords.AttendanceDate
)) TotalTime
INTO #Tmp1
FROM PairedRecords
ORDER BY 1,4;
END
SELECT * FROM dbo.Attendance
SELECT * FROM #Tmp1 ORDER BY EmpID
Check who was at the office at a specific date and time:
-- MSSQLTips.com
WITH EmployeeStatus AS (
SELECT DISTINCT
EmpID,
EmployeeName,
CASE
WHEN (
(Arrive <= '2023-10-02 11:00:00.000' AND (Depart >= '2023-10-02 11:00:00.000' OR Depart IS NULL))
OR (Arrive <= '2023-10-02 11:00:00.000' AND Depart IS NULL)
) THEN 'Yes, at office'
ELSE 'No, not at office'
END AS OfficeStatus
FROM
#Tmp1
WHERE
AttendanceDate = '2023-10-02'
)
SELECT
EmpID,
EmployeeName,
MAX(OfficeStatus) AS OfficeStatus
FROM
EmployeeStatus
GROUP BY
EmpID,
EmployeeName
ORDER BY EmpID;
Next Steps
- Read the previous articles:
About the author
Jan Potgieter has more than two decades of expertise in the database industry as a Certified Microsoft SQL Server Administrator and Database Developer.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2024-01-22
