Pre Condition: [I am using Week as Sunday as first day of week] I have table '#TT' as below
Column -> D_Date
----------
2020-12-27 |
2020-12-28 |
2020-12-29 |
2020-12-30 |
2020-12-31 |
2021-01-01 |
2021-01-02 |
I want to get week numbers for date And in case of year end with new year start, I want to mark the first week as 0 for new year if it's days contribution is less than 4 days i.e.
As per US week
2020-12-27 from this **53rd** start
2020-12-28
2020-12-29
2020-12-30
2020-12-31
2021-01-01
2021-01-02 and end at here
In above from 27 till 31 are 5 days of 2020 whereas for 1 and 2 of 2021 are 2 days, thus majority days are from 2020 and not 2021. Now I want to mark week number for 2021 as 0 for dates 1 & 2 and not 1
I am using below query but it is decreasing my minimum year's week number too. How to achieve this? Please help me.
SELECT
DATEPART( week, D_Date ),
CASE
WHEN COUNT( DATEPART( week, D_Date ) ) > 1 THEN MAX( DATEPART(week, D_Date ) - 1 )
ELSE DATEPART( week, D_Date )
END AS [State]
FROM
#tt
GROUP BY
DATEPART( year, D_Date ),
DATEPART( week, D_Date );