SQL-Looping

Database
Enthusiast

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

Tags (1)
3 REPLIES
Enthusiast

Re: SQL-Looping

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

Senior Supporter

Re: SQL-Looping

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');
Enthusiast

Re: SQL-Looping

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.