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


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
******************************************************************************************************************************************************/

 

Rate This
[Total: 5    Average: 4.2/5]

Gopal Krishna Ranjan

About Gopal Krishna Ranjan

Gopal has 8 years of industry experience in Software development. He has a head down experience in Data Science, Database, Data Warehouse, Big Data and cloud technologies and has implemented end to end solutions. He has extensively worked on SQL Server, Python, Hadoop, Hive, Spark, Azure, Machine Learning, and MSBI (SSAS, SSIS, and SSRS). He also has good experience in windows and web application development using ASP.Net and C#.


Leave a comment

Your email address will not be published. Required fields are marked *

One thought on “Unfolding | Set Based Approach | Create Attendance Report – Episode 7