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.