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

 WeekStart WeekEnd 01-12-2015 06-12-2015 07-12-2015 13-12-2015 14-12-2015 20-12-2015 21-12-2015 27-12-2015 28-12-2015 31-12-2015

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

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