All Forums Database
N_Raghu 34 posts Joined 12/13
14 Oct 2014
SQL-Looping

Hi all,
I want to acheive something which I scaled down to below sql,
insert into t1
sel 'annual',sum(a) from t2 where EOM_DT between '2013-10-01' and '2014-09-30';
insert into t1
sel 'SemiAnnual',sum(a) from t2 where EOM_DT between '2014-04-01' and '2014-09-30';
insert into t1
sel 'Quarterly',sum(a) from t2 where EOM_DT between '2014-07-01' and '2014-09-30';
So based on date parameters I need to calculate aggregates. Even the date patameters should be created based on given date.
Is there a better way of doing this may be using looping or recursion? instead writing individual queries

-Raghu
Tags:
Raja_KT 1246 posts Joined 07/09
14 Oct 2014

You can think of putting all in a subquery and union all, giving alias to all fields.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
14 Oct 2014

You should avoid multiple scans over the data - its waisting IO and CPU
Try something like:

replace macro yourdb.agg_demo (ref_date date)
as
(
select case when c.id = 1 then 'annual' 
                        when c.id = 2 then 'SemiAnnual'
                        when c.id = 3 then 'Quarterly'
              end as report_period,
              case when c.id = 1 then t.annual
                        when c.id = 2 then t.SemiAnnual
                        when c.id = 3 then t.Quarterly
              end as report_value
from 
(
select sum(case  when calendar_date between add_months(:ref_date+1,-12) and :ref_date then day_of_calendar else 0 end) as annual,
             sum(case  when calendar_date between add_months(:ref_date+1,-6) and :ref_date then day_of_calendar else 0 end) as SemiAnnual,
             sum(case  when calendar_date between add_months(:ref_date+1,-3) and :ref_date then day_of_calendar else 0 end) as Quarterly
 from sys_calendar.calendar
 where calendar_date between add_months(:ref_date+1,-12) and :ref_date
) as t
cross join
(
select day_of_calendar as id
from sys_calendar.calendar
where id between 1 and 3
) as c
;
);

exec yourdb.agg_demo('2014-09-30');
             

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

N_Raghu 34 posts Joined 12/13
15 Oct 2014

Thank You for your responses Raja_KT and Ulrich.
I inserted summaries for each month in to a volatile table and then used union all. In this way I thought I can keep it simple.

-Raghu

You must sign in to leave a comment.