DB2 SQL: How to select all days in a date range even if no data exists for some days

Question

I am really really new to the whole programming world.

I have found this previously. However I need rolling dates and into a SQL i am not aware what it is. Example of the SQL im using:

Select dd.date, dd.week, sum(p.policy_count) Policies,
from db.policies p
join global.dim_date dd on dd.date = p.trans_date
Where dd.date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 day from global.TODAY_DATE)
Group by dd.date, dd.week

I tried plugging in the recursive CTE from the other thread but it errors out the whole time and I ma not sure of how to adapt it to DB2 language and make it rolling.

Any help much appreciated. Thanks

EDIT: I have been playing around and the temporary workaround is that we have a separate date table that in itself is counted as a value and can thus be joined as a union all to allow the 0 values to stand. However the closest I came without that is with:

select 
dd.date, coalesce(sum(x.policy_count),0) policy_count, sum(x.POLICY_GROSS) TY_Gross, sum(x.POLICY_COMMISSION) TY_Comm

from global.dim_date as dd left join 
( select p.trans_date, sum(p.policy_count) policy_count, sum(p.POLICY_GROSS) TY_Gross, sum(p.POLICY_COMMISSION) TY_Comm

from db.policies p join db.agents a on a.agent = p.agent

where p.trans_date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 days from global.TODAY_DATE)
group by p.trans_date ) AS x on x.trans_date = dd.date

Where dd.date between (select week - 112 days from global.TODAY_DATE) and (select week - 1 day from global.TODAY_DATE)

group by dd.date

This does not work because it rejects p.policy_gross and p.policy_commission and returns the coalesce values as 1 instead of 0.

As per comment below I am not sure how to identify tables and I have asked around and no one knows what DB2 it is, I emailed the devs so will update that soon

EDIT 2: Tables

Dates = global.dim_date dd
Policies = db.policies p
Agents = db.agents a

When I add extra constraints after 'where dd.date..' e.g.

Where dd.date between (select date - 112 days from global.TODAY_DATE) 
and (select date - 1 day from global.TODAY_DATE)
and a.agent in ('xxxxx','yyyyy')

When before all available dates, even with zeros, were returned are now omitted and i believe its because of the and clause.

Answer 1

You say you have a date table. You don't give a name so I shall refer to it as DateTable and the column in it as TheDate. You need to select from this table and LEFT OUTER JOIN to it. That way all dates in the range will have a row in the output with NULL for rows where you don't have "real" data. You can convert these to zero using COALESCE. I'll use your first posted query as an example:

Select 
    dd.date,
    dd.week,
    sum(COALESCE(p.policy_count, 0)) Policies
from DateTable dt  -- Your date table
left outer join magenta.policies p -- left join includes all dates and any polices that exist
    on p.trans_date = dt.TheDate
left outer join global.dim_date dd
    on dd.date = p.trans_date
-- use DateTable in the WHERE to get the full range of time.
Where dt.TheDate between (select week - 112 days from global.TODAY_DATE)
                  and (select week -   1 day from global.TODAY_DATE)
Group by
    dd.date,
    dd.week

Sorry, don't have a DB2 instance to hand to test it. Forgive any typos I may have introduced.

Answer 2

It's kinda lame to have to keep static table(s) with "every date forever", so you can get around the issue, and still report dates even if they have no data, by creating & joining to a temp table, dynamically created based on your data range, and query against that on the first step of a complex queryset.

This will handle creation of the temp table:

declare @startdate date = '20180401'
declare @enddate date = '20180701'
create table #datelist ( [myDate] datetime )

while (@startdate <= @enddate) begin
  insert into #datelist
  select top 1 @startdate as [myDate]
  set @startdate = dateadd(d, 1, @startdate)
end


-----------------------
select * from #datelist

There's a Demo on SEDE, here along with other date options and further information.