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:

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

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 [DATEIN (' + @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