How to Get Number of Saturday's and sunday's In SQL USING CTE
How to use CTE(Common Table Expression) in sql server and simple examples related in CTE and how to Get Number of Saturday's and Sunday's In a month USING CTE(Common Table Expression), as well as which dates Saturday's and Sunday's occurs in a month. Here we can use CTE along with OPTION(MAXRECURSION 100(COUNTS)) Keyword.
Simple Example in CTE
WITH CTE(DATE, Cnt)
AS
(
SELECT GETDATE(), 1
UNION ALL
SELECT DATE+1, Cnt+1 FROM CTE WHERE Cnt < 30
)
SELECT DATE, cnt FROM CTE OPTION(MAXRECURSION 30)
Below 100 rows in a CTE we need not use MAXRECURSION Keyword
The OPTION(MAXRECURSION 32767) max no of count 32767
I'm going to use CTE(Common Table Expression) for get recursive data, and we can see how to get number of saturday's and sunday's in a month
WITH CTE(DATE, Cnt)
AS
(
SELECT DATEADD(DAY, -DAY(GETDATE()-1), GETDATE()), 1
UNION ALL
SELECT DATE+1, Cnt+1 FROM CTE WHERE Cnt < (SELECT DAY(DATEADD(DAY, -DAY(DATEADD(MM, 1, GETDATE())), DATEADD(MM, 1, GETDATE()))))
)
--Here I am inserting CTE Data into Temporary table and then we can get exact
data from Temptable
SELECT DATENAME(WEEKDAY, DATE) AS WEEKDAYS, cnt INTO #MaskTable FROM CTE OPTION(MAXRECURSION 30)
SELECT COUNT(*) FROM #MaskTable WHERE WEEKDAYS IN('Saturday', 'Sunday')
SELECT * FROM #MaskTable WHERE WEEKDAYS IN('Saturday', 'Sunday')
How to get number of days in month:
SELECT DAY(DATEADD(DAY, -DAY(DATEADD(MM, 1, GETDATE())), DATEADD(MM, 1,
GETDATE())))
Dear Friend,
Really good collection....thanks for posting such great articles....keep contributing.....