I am using Covid world data, which tracks number of new_cases every day. I have data from 2020-01-01 to present day. This is my current query:

SELECT MIN(date) as week, datepart(iso_week, date) week_num,  sum(new_cases) as [Total Cases]
FROM covid_data_cleaned
GROUP BY DATEPART(iso_week, date), DATEPART(year, date)
ORDER BY MIN(date) DESC

Which gives me pretty much what I want except for when the year changes:

week week_num Total Cases
2022-01-10  2   20803473
2022-01-03  1   17217248
**2022-01-01    52  2115780**
**2021-12-27    52  7971560**
2021-12-20  51  5561762

I want to figure out the workaround to combining the '52' values together. Alternatively, my dataset has 112 weeks; can I assign the values 1-112 to the whole dataset rather than 1-52 for each year?

CodePudding user response：

You could aggregate on a correction via a CASE WHEN for the iso year.

SELECT
  min(weekstart) as [week] 
, [iso_week] as weeknum
, sum(new_cases) as [Total Cases] 
FROM
(
    SELECT 
      min([date]) as weekstart
    , datepart(iso_week, [date]) as [iso_week] 
    , case 
      when month(min([date])) = 1 
       and datepart(iso_week, [date]) >= 52
      then year([date]) - 1 
      when month(min([date])) = 12
       and datepart(iso_week, [date]) = 1
      then year([date])   1 
      else year([date]) 
      end as iso_year
    , sum(new_cases) as new_cases
    FROM covid_data_cleaned
    GROUP BY year([date]), datepart(iso_week, [date])
) q
GROUP BY iso_year, [iso_week] 
ORDER BY [week] DESC;

CodePudding user response：

Instead of iso week, you can use week.

DECLARE @table table(datev date, new_Cases int)

INSERT INTO @table values
('2022-01-01',123),('2022-01-09',432),('2022-01-03',123),('2021-12-27',234);

SELECT MIN(datev) as week, datepart(week, datev) week_num,  sum(new_cases) as [Total Cases]
FROM @table
GROUP BY DATEPART(week, datev), DATEPART(year, datev)
ORDER BY MIN(datev) DESC