Get Monthly Attendance Report by Stored Procedure in SQL

Introduction 

This article will help to find the attendance report (in tabular format) of all the students/employees for the range between two dates, whether they were present or absent, using a Stored Procedure in SQL Server.

Sample Output

NAME	2016-07-01	2016-07-02	2016-07-03	2016-07-04	2016-07-05	2016-07-06	2016-07-07
A	PRESENT	PRESENT	PRESENT	N/A	N/A	N/A	N/A
M	PRESENT	PRESENT	PRESENT	N/A	N/A	N/A	N/A

Here I used one table that holds the attendance status of each student. The output mainly comes in a tabular format. To get the required output I used a Dynamic Pivot in SQL. 

Before that, we need to understand what a Pivot is. Mainly a Pivot is a technique to rotate the data from a row to a column. 

The following are some good references for Pivots:

• Simple Way To Use Pivot In SQL Query
• Using PIVOT and UNPIVOT

Here also a Dynamic Query is used (to generate the SQL code programmatically).

• Introduction to Dynamic SQL (Part 1)

Using the code

First create a table as in the following:

1. CREATE TABLE TMP  
2. (  
3.    NAME VARCHAR(50),  
4.    DATE DATETIME ,  
5.    PRESENT_STATUS VARCHAR(10)  
6. )  

Now insert data into the table. Here I inserted records by datw:

JULY

1. INSERT INTO TMP VALUES ('A','2016-07-01','PRESENT')  
2. INSERT INTO TMP VALUES ('M','2016-07-01','PRESENT')  
3. INSERT INTO TMP VALUES ('A','2016-07-02','PRESENT')  
4. INSERT INTO TMP VALUES ('M','2016-07-02','ABSENT')  
5. INSERT INTO TMP VALUES ('A','2016-07-03','PRESENT')  
6. INSERT INTO TMP VALUES ('M','2016-07-03','PRESENT')  

AUGUST

1. INSERT INTO TMP VALUES ('A','2016-08-01','ABSENT')  
2. INSERT INTO TMP VALUES ('M','2016-08-01','PRESENT')  
3. INSERT INTO TMP VALUES ('A','2016-08-02','ABSENT')  
4. INSERT INTO TMP VALUES ('M','2016-08-02','ABSENT')  
5. INSERT INTO TMP VALUES ('A','2016-08-03','ABSENT')  
6. INSERT INTO TMP VALUES ('M','2016-08-03','PRESENT')  

Now create a Stored Procedure with two datetime input parameters.

In the SP, dates are genarated between two dates using a Common Table Expression and then they are added one by one separated by commas. Finally make the dynamic SQL to get the output.

1. CREATE PROCEDURE GET_ATTENDANCEREPORT   
2. @STARTDATE DATETIME,  
3. @ENDDATE DATETIME  
4. AS BEGIN  

Now generate the dates between two dates using a Common Table Expression and store the values in one temporary table (#TMP_DATES).

1. WITH DATERANGE AS  
2. (  
3.    SELECT DT =DATEADD(DD,0, @STARTDATE)  
4.    WHERE DATEADD(DD, 1, @STARTDATE) <= @ENDDATE  
5.    UNION ALL  
6.    SELECT DATEADD(DD, 1, DT)  
7.    FROM DATERANGE  
8.    WHERE DATEADD(DD, 1, DT) <= @ENDDATE  
9. )  
10. SELECT * INTO #TMP_DATES  
11. FROM DATERANGE   

Since the report columns (Dates) are dynamic, hence the columns (Dates) are concatenated one by one from the temporary table (#TMP_DATES) and stores the value in a local variable.

1. DECLARE @COLUMN VARCHAR(MAX)  
2. SELECT @COLUMN=ISNULL(@COLUMN+',','')+ '['+ CAST(CONVERT(DATE , T.DT) AS VARCHAR) + ']' FROM #TMP_DATES T  

After the Pivot, some columns may be null if the data (here PRESENT_STATUS) does not exist in the pivot section. Now replace the null values by "N/A".

1. DECLARE @Columns2 VARCHAR(MAX)  
2. SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+ CAST(CONVERT(DATE , DT) as varchar )+'],''N/A'') AS ['+CAST(CONVERT(DATE , DT) as varchar )+']' FROM #TMP_DATES GROUP BY dt FOR XML PATH('')),2,8000)  

Now declare one local variable to write the dynamic SQL query.

1. DECLARE @QUERY VARCHAR(MAX)  

Here a right outer-join is used to show the all dates from the temporary table:

1. SET @QUERY = 'SELECT NAME, ' + @Columns2 +' FROM   
2. (  
3. SELECT A.NAME , B.DT AS DATE, A.PRESENT_STATUS FROM TMP A RIGHT OUTER JOIN #TMP_DATES B ON A.DATE=B.DT   
4. ) X  
5. PIVOT   
6. (  
7. MIN([PRESENT_STATUS])  
8. FOR [DATE] IN (' + @COLUMN + ')  
9. ) P   
10. WHERE ISNULL(NAME,'''')<>''''  
11. '  
12.   
13. EXEC (@QUERY)  

Drop the temporary table.

1. DROP TABLE #TMP_DATES  
2.   
3. END  

Now execute the Stored Procedure.

1. EXEC dbo.GET_ATTENDANCEREPORT @STARTDATE ='2016-07-01',  
2. @ENDDATE='2016-07-7'  

The output will be as in the following:

NAME	2016-07-01	2016-07-02	2016-07-03	2016-07-04	2016-07-05	2016-07-06	2016-07-07
A	PRESENT	PRESENT	PRESENT	N/A	N/A	N/A	N/A
M	PRESENT	PRESENT	PRESENT	N/A	N/A	N/A	N/A