Post
Ask Question

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.

Find more about Stored Procedure in SQL Server- Stored Procedure

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.

CREATE TABLE TMP  
(  
   NAME VARCHAR(50),  
   DATE DATETIME ,  
   PRESENT_STATUS VARCHAR(10)  
)  

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

JULY

INSERT INTO TMP VALUES ('A','2016-07-01','PRESENT')  
INSERT INTO TMP VALUES ('M','2016-07-01','PRESENT')  
INSERT INTO TMP VALUES ('A','2016-07-02','PRESENT')  
INSERT INTO TMP VALUES ('M','2016-07-02','ABSENT')  
INSERT INTO TMP VALUES ('A','2016-07-03','PRESENT')  
INSERT INTO TMP VALUES ('M','2016-07-03','PRESENT')  

AUGUST

INSERT INTO TMP VALUES ('A','2016-08-01','ABSENT')  
INSERT INTO TMP VALUES ('M','2016-08-01','PRESENT')  
INSERT INTO TMP VALUES ('A','2016-08-02','ABSENT')  
INSERT INTO TMP VALUES ('M','2016-08-02','ABSENT')  
INSERT INTO TMP VALUES ('A','2016-08-03','ABSENT')  
INSERT INTO TMP VALUES ('M','2016-08-03','PRESENT')  

Now create a Stored Procedure with two DateTime input parameters.

In the SP, dates are generated 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.

CREATE PROCEDURE GET_ATTENDANCEREPORT   
@STARTDATE DATETIME,  
@ENDDATE DATETIME  
AS BEGIN  

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

WITH DATERANGE AS  
(  
   SELECT DT =DATEADD(DD,0, @STARTDATE)  
   WHERE DATEADD(DD, 1, @STARTDATE) <= @ENDDATE  
   UNION ALL  
   SELECT DATEADD(DD, 1, DT)  
   FROM DATERANGE  
   WHERE DATEADD(DD, 1, DT) <= @ENDDATE  
)  
SELECT * INTO #TMP_DATES  
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 store the value in a local variable.

DECLARE @COLUMN VARCHAR(MAX)  
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 with "N/A".

DECLARE @Columns2 VARCHAR(MAX)  
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.

DECLARE @QUERY VARCHAR(MAX)  

Here a right outer join is used to show the all dates from the temporary table. Find more about temporary tables here-  Temporary Table 

SET @QUERY = 'SELECT NAME, ' + @Columns2 +' FROM   
(  
SELECT A.NAME , B.DT AS DATE, A.PRESENT_STATUS FROM TMP A RIGHT OUTER JOIN #TMP_DATES B ON A.DATE=B.DT   
) X  
PIVOT   
(  
MIN([PRESENT_STATUS])  
FOR [DATE] IN (' + @COLUMN + ')  
) P   
WHERE ISNULL(NAME,'''')<>''''  
'  
  
EXEC (@QUERY)  

Drop the temporary table.

DROP TABLE #TMP_DATES  
  
END  

Now execute the Stored Procedure.

EXEC dbo.GET_ATTENDANCEREPORT @STARTDATE ='2016-07-01',  
@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

Conclusion

In this article, we learned about how to get monthly attendance report by Stored Procedure in SQL.


Similar Articles