Hi, now we
will see the date calendar of month using CTE (common table expression) and PIVOT table.
Just follow
the code:
USE tempdb
GO
----------- Function
to return the First Date for month ---------
IF EXISTS(SELECT * FROM SysObjects WHERE
NAME = 'f_FirstDayOfMonth'
AND XTYPE = 'FN')
BEGIN
DROP FUNCTION [f_FirstDayOfMonth]
END
GO
CREATE FUNCTION [dbo].[f_FirstDayOfMonth]
(@DATE DATE)
RETURNS DATE
AS
BEGIN
DECLARE @ANSWER DATE, @MONTH VARCHAR(2), @YEAR CHAR(4)
SET @MONTH = CASE WHEN DATEPART(MONTH,@DATE) < 10 THEN '0' ELSE '' END + CONVERT(VARCHAR(2), DATEPART(MONTH,@DATE))
SET @YEAR = CONVERT(CHAR(4),DATEPART(YEAR,@DATE))
SET @ANSWER = CONVERT(DATE,@MONTH + '/01/' + @YEAR)
RETURN @ANSWER
END
GO
----------- Function to return the Last Date for month --------
IF EXISTS(SELECT * FROM SysObjects WHERE
NAME = 'f_LastdayOfMonth'
AND XTYPE = 'FN')
BEGIN
DROP FUNCTION [f_LastdayOfMonth]
END
GO
CREATE FUNCTION [DBO].[f_LastdayOfMonth]
(@DATE DATE)
RETURNS DATE
AS
BEGIN
DECLARE @ANSWER DATE, @MONTH VARCHAR(2), @YEAR CHAR(4)
SET @MONTH = CASE WHEN DATEPART(MONTH,@DATE)<10 THEN '0' ELSE '' END + CONVERT(VARCHAR(2),DATEPART(MONTH,@DATE))
SET @YEAR = CONVERT(CHAR(4),DATEPART(YEAR,@DATE))
SET @ANSWER = CONVERT(DATE,@MONTH + '/01/' + @YEAR)
SET @ANSWER = DATEADD(MONTH,1,@ANSWER)
SET @ANSWER = DATEADD(DAY,-1,@ANSWER)
RETURN @ANSWER
END
GO
--------------- Week Date Calender --------------------------------
IF EXISTS(SELECT * FROM SysObjects WHERE
NAME = 'Calendar'
AND XTYPE = 'P')
BEGIN
DROP PROCEDURE [Calendar]
END
GO
CREATE PROCEDURE [dbo].[Calendar]
(@MONTH TINYINT,
@YEAR INT
)
AS
BEGIN
DECLARE @DATE1 DATE,
@ENDDATE DATE,
@DAY1 VARCHAR(10),
@WEEKID TINYINT,
@CURRDATE DATE
SELECT @CURRDATE= CONVERT(DATE,(CAST(@YEAR AS CHAR(4))+'-'+CAST(@MONTH AS VARCHAR(2))+'-15'))
SELECT @DATE1=CONVERT(DATE,[dbo].[f_FirstDayOfMonth](@CURRDATE)),@ENDDATE=CONVERT(DATE,[dbo].[f_LastdayOfMonth](@CURRDATE))
SELECT @DAY1= DATENAME(WEEKDAY, @DATE1)
----------
Recursive CTE to get Days and Dates for the month
-----------------
;WITH CTE_CAL ([DATE], [DAY], [WEEKID])
AS
(
SELECT @DATE1, @DAY1, CASE WHEN DATEPART(WEEKDAY,@DATE1)=1 THEN CAST(DATEPART(WW,@DATE1) AS TINYINT)-1
ELSE DATEPART(WW,@DATE1) END AS WEEKID
UNION ALL
SELECT DATEADD(DD,1,[DATE]), CAST(DATENAME(WEEKDAY, DATEADD(DD,1,[DATE])) AS VARCHAR(10)),
CASE WHEN DATEPART(WEEKDAY,DATEADD(DD,1,[DATE]))=1 THEN CAST(DATEPART(WW,DATEADD(DD,1,[DATE]))AS TINYINT)-1
ELSE DATEPART(WW,DATEADD(DD,1,[DATE])) END AS WEEKID
FROM CTE_CAL
WHERE [DATE] < @ENDDATE
)
------------- Use
Pivot to display the result in calendar format
-----------
SELECT [WEEKID],[MONDAY],[TUESDAY],[WEDNESDAY],[THURSDAY],[FRIDAY],[SATURDAY],[SUNDAY]
FROM
(SELECT [WEEKID], [DATE], [DAY] FROM CTE_CAL)
PVT
PIVOT
(MAX([DATE]) FOR [DAY] IN ([MONDAY],[TUESDAY],[WEDNESDAY],[THURSDAY],[FRIDAY],[SATURDAY],[SUNDAY]))
PVTTAB
END
GO
Now I write
this code another way, most simple way…
DECLARE @MONTH INT = 08
DECLARE @YEAR INT = 2016
;WITH CTE_CAL ([DATE], [DAY])
AS
(SELECT CONVERT(DATE,CONVERT(VARCHAR,(((@YEAR*100)+@MONTH)*100)+1)),
DATENAME(WEEKDAY,CONVERT(DATE,CONVERT(VARCHAR,(((@YEAR*100)+@MONTH)*100)+1)))
UNION ALL
SELECT DATEADD(DD,1,[DATE]),
DATENAME(WEEKDAY, DATEADD(DD,1,[DATE]))
FROM CTE_CAL
WHERE MONTH(DATEADD(DD,1,[DATE])) = @MONTH
)
SELECT *
FROM (SELECT DATEPART(WEEK,[DATE])'WeekID',* FROM CTE_CAL) V1
PIVOT
(MAX([DATE]) FOR [DAY]
IN
(Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday))AS PVT
If there is any other better option,
Thank You,
Arka Gupta.
No comments:
Post a Comment