# 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(), 1UNION ALLSELECT 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()), 1UNION ALLSELECT 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 exactdata 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())))`

Author: Ultimaterengan17 Apr 2012 Member Level: Gold   Points : 0

Dear Friend,
Really good collection....thanks for posting such great articles....keep contributing.....

Author: Rakesh Chaubey20 May 2013 Member Level: Gold   Points : 0

Nice

Guest Author: Ninda28 May 2013

I have only one head accident in my life. I was 8 years old and I fell down from a monkey bar at school and got 8 stitches in the back of my head. I just came across this today. I have been diagnosed with Anxiety Disorder and I have had depression on and off for about 3 years. Is it possible that I could CTE from one accident? I am seriously worried and starting to panic.

• Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
• No HTML formatting and links to other web sites are allowed.
• This is a strictly moderated site. Absolutely no spam allowed.
• Name:
Email: