Today, I helped someone in one of the forum about above question. He wanted to find first and last Friday of every month from 1st Jan 2006 to 31st Dec 2020. This is bit a challenging job. There are quite a few ways to do so like temp table, CTE etc. and I have had my hand in CTE to solve this problem. Let us see the script itself.
WITH CTE AS
(
select CONVERT(datetime,'01/06/2006') as dt, datename(dw,CONVERT(datetime,'01/06/2006')) as dy,datename(mm,CONVERT(datetime,'01/06/2006')) as mn, YEAR(CONVERT(datetime,'01/06/2006')) AS yr
union all
select s.dt+7 as dts,datename(dw,dt+7) as dy ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
from cte s
where s.dt<CONVERT(datetime,'12/31/2020')
)
select MAX(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
UNION
select MIN(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
ORDER BY dt
Quite a simple to do so in CTE but if you will try to attempt above query, you will be greeted by following error.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
How to fix that annoying error? It is crossing default recursion limit of SQL Server which is 100 and above CTE will iterate much more time than 100 so obviously you are going to face an error. All you need to do is, just have one clause after above CTE, OPTION (MaxRecursion 0). So finally query will looks like
WITH CTE AS
(
select CONVERT(datetime,'01/06/2006') as dt, datename(dw,CONVERT(datetime,'01/06/2006')) as dy,datename(mm,CONVERT(datetime,'01/06/2006')) as mn, YEAR(CONVERT(datetime,'01/06/2006')) AS yr
union all
select s.dt+7 as dts,datename(dw,dt+7) as dy ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
from cte s
where s.dt<CONVERT(datetime,'12/31/2020')
)
select MAX(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
UNION
select MIN(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
ORDER BY dt
OPTION (MaxRecursion 0)
Enjoy!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
1 comments:
Here's something I use...
SELECT DATEADD(day, (DATEDIFF (day, '2010-01-01', (SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)))) / 7) * 7, '2010-01-01') AS "LAST_FRIDAY_OF_MONTH"
Post a Comment