Daily Archives: Aug 12, 2016


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 
More