CODEWITHSUNDEEP
sqlsql-serverdateaggregate
Michael
Michael

Reputation: 37

How to add a set of dates for each category in a dimension?

I have data that looks like this where there is a monthly count of a particular animal for each month. By default, it aggregates in the month where there is data.

However, I would like to like to have a default set of dates for each animal up to the current month date with 0 if there's no data. Desired Result -

Is there a way to handle with a on sql server and not in Excel?

Much appreciated in advance.

Upvotes: 1

Views: 323

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270493

You can generate the months you want using a numbers table or recursive CTE (or calendar table). Then cross join with the animals to generate the rows and use left join to bring in the existing data:

with dates as (
      select min(date) as dte
      from t
      union all
      select dateadd(month, 1 dte)
      from dates
      where dte < getdate()
     )
select a.animal, d.dte, coalesce(t.monthly_count,  0) as monthly_count
from dates d cross join
     (select distinct animal from t) a left join
     data t
     on t.date = d.dte and t.animal = a.animal
order by a.animal, d.dte;

Upvotes: 1

Related Questions