CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
user2891487
user2891487

Reputation: 75

Aggregate totals and create data for weeks that no data exists

I have a table like such:

Region     Date        Cases
France     1-1-2014      5
Spain      2-5-2014      6
France     3-5-2014      7
...

What I would like to do is run an aggregated function like so, to group the total number of cases in weeks for each region. 

    select region, datepart(week, date) weeknbr, sum(cases) cases
    from <table>
    group by region, datepart(week, date)
    order by region, datepart(week, date)

Using this aggregated function, is there a way to insert a zero value for each region when data does not exist for that week?

so the final result would look like:

region      weeknbr      cases
France      1            5
France      2            0
France      3            0
.....
Spain       1            0
Spain       2            0 
Spain       3            0
....        
Spain       8            6

I have tried to create a table with week numbers, and then joining the week numbers with my data, but have been unsuccessful. This ends up creating a null or zero value for the region and cases. I can always use the isnull function to make the cases 0, but I need to account for each region for each week. That's whats killing me right now. Is this possible? If not, where should I start looking and how should I modify the underlining tables?

Any help would be greatly appreciated. Thank you

Upvotes: 1

Views: 71

Answers (2)

Jaaz Cole
Jaaz Cole

Reputation: 3180

If I understand your meaning correctly, you could always generate artificial rows, cross join on grouped regions for completeness of your 0's, then left join your aggregate table on region and week. So:

select r.region, w.RowId as Weeknbr, isnull(c.Cases,0)
from (
    select row_number()over(order by name) as RowID
    from master..spt_values
    ) w
    cross join (
        select region
        from <table>
        group by region
    ) r
    left join 
        select region, datepart(week, date) weeknbr, sum(cases) cases
        from <table>
        group by region, datepart(week, date)
        order by region, datepart(week, date)
    ) c on (w.RowID <= 53 and w.RowID = c.Weeknbr and r.region = c.region)

Upvotes: 2

Anon
Anon

Reputation: 10908

You need a date_list table and a region_list table. Cross join the dimension tables to get all date-region combinations and then left join against your fact table.

SELECT
  d.date,
  r.region,
  t.cases
FROM date_list d
CROSS JOIN region_list r
LEFT JOIN date_region t ON d.date = t.date AND r.region = t.region

Upvotes: 0

Related Questions