SQL : Group by with case to set values for both conditions

47 views Asked by At

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 );
0

There are 0 answers