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.

• 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.

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

SQL

Copy

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')

SQL

Copy

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')

SQL

Copy

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

SQL

Copy

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

SQL

Copy

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

SQL

Copy

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)

SQL

Copy

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

DECLARE @QUERY VARCHAR(MAX)

SQL

Copy

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)

SQL

Copy

Drop the temporary table.

DROP TABLE #TMP_DATES

END

SQL

Copy

Now execute the Stored Procedure.

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

SQL

Copy

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.