This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to find the week start day and week end day of given Month and Year.
Input data:
|
1 2 |
Month=8 Year=2017 |
Expected Output:
|
1 2 3 4 5 6 7 |
WeekStart WeekEnd ---------- ---------- 2017-08-01 2017-08-06 2017-08-07 2017-08-13 2017-08-14 2017-08-20 2017-08-21 2017-08-27 2017-08-28 2017-08-31 |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE @Month AS INT = 8 ,@Year AS INT = 2017 ;WITH CTE AS ( SELECT DISTINCT number ,DATEFROMPARTS(@Year,@Month,Number) DateValue ,DATEADD(d, (8 - datepart(WEEKDAY, DATEFROMPARTS(@Year,@Month,Number))), DATEFROMPARTS(@Year,@Month,Number)) WeekNumber FROM MASTER..SPT_Values WHERE Number > 0 AND number < DAY(EOMONTH(CONCAT(@Year,'/',@Month,'/','01'))) ) ,CTE1 AS ( SELECT CASE WHEN number = 1 THEN DATEADD(d,-1,DateValue) ELSE DateValue End DateValue ,CASE WHEN WeekNumber > EOMONTH(DateValue) THEN EOMONTH(DateValue) ELSE WeekNumber END WeekNumber FROM CTE ) SELECT MIN(DATEADD(d,1,DateValue)) as WeekStart ,WeekNumber as WeekEnd FROM CTE1 GROUP BY WeekNumber ORDER BY WeekStart |
Please try the different solution for this puzzle and share it via comment...
