I want to Mark holidays from the database and auto mark weekends to my attendance system. i have created a database for the holidays for the current year and i want to use that data to mark the holidays for the current years
Here is My Stored Procedure:
@STARTDATE DATETIME ='2021-07-01',
@ENDDATE DATETIME='2021-07-31',
@message varchar(200)=null outputAS
BEGIN
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 DECLARE @COLUMN VARCHAR(MAX)
SELECT @COLUMN=ISNULL(@COLUMN+',','')+ '['+ CAST(DAY(CONVERT(DATE , T.DT)) AS VARCHAR) + ']' FROM #TMP_DATES T
print(@COLUMN)DECLARE @Columns2 VARCHAR(MAX)
SET @Columns2 = SUBSTRING((SELECT ',ISNULL(['+ CAST(DAY(CONVERT(DATE , DT)) as varchar )+'],''A'') AS ['+CAST(DAY(CONVERT(DATE , DT)) as varchar )+']' FROM #TMP_DATES GROUP BY dt FOR XML PATH('')),2,8000)
print(@Columns2)
DECLARE @QUERY VARCHAR(MAX)SET @QUERY = 'SELECT udise as ''UDISE CODE'', ' + @Columns2 +' FROM
(
SELECT A.udise , DAY(B.DT) AS DATE, (case when A.intime is not null and A.outtime is not null then ''P'' Else ''A'' End) as [STATUS] FROM emp_attendence A RIGHT OUTER JOIN #TMP_DATES B ON DAY(A.DATE)=DAY(B.DT)
) X
PIVOT
(
MIN([STATUS])
FOR [DATE] IN (' + @COLUMN + ')
) P
WHERE ISNULL(udise,'''')<>''''
' EXEC (@QUERY) DROP TABLE #TMP_DATES
I tried a lot, but couldn't find how to solve this.