SQL Puzzle | Get Weeks (Start Date – End Date) for a particular month & year

Puzzle Statement

Recently I was asked by a developer how we can find weeks of a particular month and year. Say you want to find out weeks for December month for 2015 year.

Rules
1. Week starts from a Sunday and ends on a Saturday
2. First week start date should be first day of the month
3. Last week end date should be last day of the month

Sample Input

Month = 12 i.e. December
Year = 2015

Expected output

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION #

--


DECLARE @Mon AS INT = 12 , @Year AS INT = 2015
;WITH CTE AS
(
     SELECT DISTINCT number, DATEFROMPARTS(@Year,@Mon,Number) Dt
     ,DATEADD(d, (8 - datepart(WEEKDAY, DATEFROMPARTS(@Year,@Mon,Number))), DATEFROMPARTS(@Year,@Mon,Number)) Wk
     FROM
           MASTER..SPT_Values
     WHERE
          Number > 0 AND number < DAY(EOMONTH(CONCAT(@Year,'/',@Mon,'/','01'))) ),
          CTE1 AS ( SELECT CASE WHEN number = 1 THEN DATEADD(d,-1,Dt) ELSE Dt End Dt 
          ,CASE WHEN Wk > EOMONTH(dt) THEN EOMONTH(dt) ELSE WK END Wk 		 
	 FROM CTE
)
SELECT MIN(DATEADD(d,1,Dt)) WeekStart , Wk WeekEnd FROM CTE1
GROUP BY Wk
ORDER BY WeekStart

--

Add a comment if you have any other solution in mind. I would love to learn it.

We all need to learn.

Enjoy !!! Keep Learning

Pawan Kumar Khowal 

Http://MSBISkills.com