2

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.

  • You'll need to provide the definitions of your tables, some sample data, and the desired results. Also describe your DB2 version and platform. – mustaccio May 20 '14 at 14:51
  • Thats the thing I am asking around and no one can give me a straight answer. I have found that nesting works works only if i dont constrain it otherwise it still returns only where is data. for e.g. ill update post. – sneekee May 21 '14 at 9:39
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.

  • Thanks, I had a look and it does work so thanks for that. I misunderstood the problem myself. I blamed the dates for not returning the 0. However after the where clause, when I add additional constraints it again omits the dates on their own because it is following the extra constraints that I added. This is like solving trigonometric functions all over again, there are so many outcomes – sneekee May 22 '14 at 12:38
1

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.

img

  • You realize, of course, that the question is about Db2, not SQL Server, so your solution won't work as it uses incompatible syntax. – mustaccio Jul 11 '18 at 11:55
  • really..... d'oh! ...and I've never used Db2 but there must be something somewhat comparable, rather than housing static tables ...? – ashleedawg Jul 11 '18 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.