Unfolding | Set Based Approach | Create Attendance Report – Episode 7

In this video, we are going to learn;

1. Generate date list from the given date range dynamically
2. Repeat the date list for each employee without using a loop or recursive CTE

You can download the script used in this example from below the video.

/******************************************************************************************************************************************************
Start using the SetBasedApproach database
******************************************************************************************************************************************************/
--Start using the existing database SetBasedApproach which has been already created in this series
USE SetBasedApproach
GO

/******************************************************************************************************************************************************
Create required objects
******************************************************************************************************************************************************/
--Drop the EmployeeMaster table if it exists already in the database
IF(OBJECT_ID('dbo.EmployeeMaster') IS NOT NULL)
DROP TABLE dbo.EmployeeMaster

--Create Employee master table
CREATE TABLE dbo.EmployeeMaster
(
EmpID INT NOT NULL PRIMARY KEY,
EmpName NVARCHAR(255) NOT NULL
)

--Drop the EmployeeAttendanceDetail table if it exists already in the database
IF(OBJECT_ID('dbo.EmployeeAttendanceDetail') IS NOT NULL)
DROP TABLE dbo.EmployeeAttendanceDetail

--Create EmployeeAttendanceDetail table which stores the attendance marked by the employees
CREATE TABLE dbo.EmployeeAttendanceDetail
(
RowId INT NOT NULL IDENTITY(1, 1),
CalendarDate DATE NOT NULL,
EmpID INT NOT NULL, --REFERENCES dbo.EmployeeMaster(EmpID)
AttendanceMarkedOn DATETIME,
)

/******************************************************************************************************************************************************
Insert sample data
******************************************************************************************************************************************************/
--Add data into employee master table
INSERT dbo.EmployeeMaster ([EmpID], [EmpName])
VALUES (1, N'Adam'),
(2, N'Joy'),
(3, N'Roy'),
(4, N'Mac')

--Insert the sample rows in the EmployeeAttendanceDetail table now
INSERT INTO dbo.EmployeeAttendanceDetail
(CalendarDate, EmpID, AttendanceMarkedOn)
VALUES
('20160523', '1', '20160523 9:10:00'),
('20160523', '2', '20160523 10:10:00'),
('20160523', '3', '20160523 8:10:00'),

('20160524', '1', '20160524 9:15:00'),
('20160524', '3', '20160524 10:15:00'),

('20160525', '1', '20160525 9:05:00'),
('20160525', '3', '20160525 10:05:00'),
('20160525', '4', '20160525 8:10:00'),

('20160527', '4', '20160527 10:10:00'),

('20160530', '1', '20160530 9:05:00'),
('20160530', '2', '20160530 11:15:00'),
('20160530', '4', '20160530 10:15:00')

/******************************************************************************************************************************************************
Generate date list
******************************************************************************************************************************************************/
--Have a look on the data of EmployeeAttendanceDetail table
SELECT RowId, CONVERT(VARCHAR(20), CalendarDate, 106) AS CalendarDate, EmpID, AttendanceMarkedOn
FROM dbo.EmployeeAttendanceDetail

--Have a look on the tally table and its data which has been created earlier
SELECT * FROM dbo.Tally

--Calculate the difference between start and end date in terms of days
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT DATEDIFF(DD, @StartDate, @EndDate)
GO

--Calculate the difference between start and end date in terms of days and then add 1
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT DATEDIFF(DD, @StartDate, @EndDate) + 1
GO

--To get the required number of rows from tally table, we can use this query
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT N FROM DBO.Tally WHERE N <= DATEDIFF(DD, @StartDate, @EndDate) + 1
GO

--To compute the value of date in each row, add the value of N in date part of the start date
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT
DATEADD(DD, N, @StartDate) AS AttendanceDate
FROM DBO.Tally WHERE N <= (DATEDIFF(DD, @StartDate, @EndDate)) +1
GO

--The date value generated after adding N in the day part of the date value is increasing by 1 day.
--So, add N -1 instead of N to get the required date value in each row
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT
DATEADD(DD, N - 1, @StartDate) AS AttendanceDate
FROM DBO.Tally WHERE N <= (DATEDIFF(DD, @StartDate, @EndDate)) +1
GO
/******************************************************************************************************************************************************
Generate superset now
******************************************************************************************************************************************************/
--To create a super set of employees and dates, we can cross join the date list with employee master table as this
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT DATEADD(DD, T.N - 1, @StartDate) AS AttendanceDate, EmpID, EmpList.EmpName
FROM DBO.Tally T
CROSS JOIN dbo.EmployeeMaster EmpList
WHERE N <= (DATEDIFF(DD, @StartDate, @EndDate)) + 1
ORDER BY AttendanceDate, EmpId
GO

/******************************************************************************************************************************************************
Left join the superset to the subset
******************************************************************************************************************************************************/
--To identify whether an employee was present on a specific date or not, lets join the created super set of dates and emploees with EmployeeAttendanceDetail
--table with a left join as this
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT
DATEADD(DD, T.N - 1, @StartDate) AS AttendanceDate, EmpList.EmpID, EmpList.EmpName, EAD.AttendanceMarkedOn
FROM DBO.Tally T
CROSS JOIN dbo.EmployeeMaster EmpList
LEFT JOIN dbo.EmployeeAttendanceDetail EAD
ON EAD.CalendarDate = DATEADD(DD, T.N - 1, @StartDate) AND EmpList.EmpID = EAD.EmpID
WHERE N <= (DATEDIFF(DD, @StartDate, @EndDate)) + 1
GO

/******************************************************************************************************************************************************
Display P, or A conditionally with CASE expression
******************************************************************************************************************************************************/
--We can use a CASE expression to display the status of an employee on each date as presnet, or absent like this
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT DATEADD(DD, T.N - 1, @StartDate) AS AttendanceDate, EmpList.EmpID,
--Case epression to display P and A
CASE WHEN EAD.AttendanceMarkedOn IS NOT NULL THEN 'P' ELSE 'A' END AS Attendance
FROM DBO.Tally T
CROSS JOIN dbo.EmployeeMaster EmpList
LEFT JOIN dbo.EmployeeAttendanceDetail EAD ON DATEADD(DD, T.N - 1, @StartDate) = EAD.CalendarDate AND EmpList.EmpID = EAD.EmpID
WHERE N <= (DATEDIFF(DD, @StartDate, @EndDate)) + 1
GO

/******************************************************************************************************************************************************
Display H, P, or A conditionally with CASE expression
******************************************************************************************************************************************************/
--We know that, to get the day name, we can use this DATENAME function in SQL Server as this
SELECT DATENAME(DW, '20160528')

--Finally, to display the status of an employee on each date as holiday, presnet, or absent, we can use below query
DECLARE @StartDate DATE = '20160523', @EndDate DATE = '20160530'
SELECT CONVERT(VARCHAR(20), DATEADD(DD, T.N - 1, @StartDate), 106) AS AttendanceDate, EmpList.EmpID,
--Case expression to display holiday, present, or absent
CASE
WHEN DATENAME(DW, DATEADD(DD, T.N - 1, @StartDate)) IN ('Saturday', 'Sunday') THEN 'H'
WHEN EAD.AttendanceMarkedOn IS NOT NULL THEN 'P'
ELSE 'A'
END
AS Attendance
FROM DBO.Tally T
CROSS JOIN dbo.EmployeeMaster EmpList
LEFT JOIN dbo.EmployeeAttendanceDetail EAD ON DATEADD(DD, T.N - 1, @StartDate) = EAD.CalendarDate AND EmpList.EmpID = EAD.EmpID
WHERE N <= (DATEDIFF(DD, @StartDate, @EndDate)) + 1
ORDER BY AttendanceDate, EmpID
GO
/******************************************************************************************************************************************************
Ends here
******************************************************************************************************************************************************/