Get All Dates Between Two Selected Date and Create These Date As Column

In this article, We learn how to get All selected dates between two dates and create these date column.

What is Pivot?

• Pivot is used to row data transfer to column.

Code

First create a table

create table EmpAttendance
(  
   Name nvarchar(50),  
   Date datetime ,  
   Status varchar(10)  
)

Now create a Stored Procedure with date range.

Create or ALter Procedure Get_AttendanceData 
@FromDate DateTIME, 
@ToDatate DateTIME 
AS BEGIN 
WITH DateRANGE AS 
( 
SELECT DT =DateADD(DD,0, @FromDate) 
WHERE DateADD(DD, 1, @FromDate) <= @ToDatate 
UNION ALL 
SELECT DateADD(DD, 1, DT) 
FROM DateRANGE 
WHERE DateADD(DD, 1, DT) <= @ToDatate 
) 
SELECT * INTO #SelectedDates 
FROM DateRANGE

Since the query Dates of columns are dynamic, hence the Dates columns are concatenated one by one from the table EmpAttendance and stores the value in a local variable.

DECLARE @COLUMN VARCHAR(MAX) 
SELECT @COLUMN=ISNULL(@COLUMN+',','')+ '['+ CAST(CONVERT(Date , T.DT) AS VARCHAR) + ']' FROM #SelectedDates T

After the Pivot, some record are empty or null we will replace this empty data into “MT”

DECLARE @Columns2 VARCHAR(MAX)  
SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+ CAST(CONVERT(Date , DT) as varchar )+'],''MT'') AS ['+CAST(CONVERT(Date , DT) as varchar )+']' FROM #SelectedDates GROUP BY dt FOR XML PATH('')),2,8000)

Now we create a pivot query

DECLARE @QUERY VARCHAR(MAX)  

SET @QUERY = 'SELECT  EmpName as Emp_Name, ' + @Columns2 +' FROM   
(  
SELECT A.EmpName , B.DT AS Date, A.Status FROM EmpAttendance A RIGHT OUTER JOIN #SelectedDates B ON A.Date=B.DT   
) X  
PIVOT   
(  
MIN([Status])  
FOR [Date] IN (' + @COLUMN + ')  
) P   
WHERE ISNULL(EmpName,'''')<>''''  
'
 
EXEC (@QUERY) 
DROP TABLE #SelectedDates 

END

Now execute this procudure

Exec Get_AttendanceData '2021-07-01','2021-07-10'

Output:

Please give your valuable feedback and if you have any questions or issues about this article, please let me know.