SQL for aggregation for current year, last year etc...

Database
Enthusiast

SQL for aggregation for current year, last year etc...

Hi Friends,

Hope you all are doing good.

I have one requirement to aggregate for different time periods. To make requirement things clear, I have given below Input, Rules and the expected output.

 

Input
accountdriver dateamountactiverenewal1renewal2previous year amountcurrent year amountrenewal amountfull amount
11111111112014-01-22902014-05-202015-07-202016-07-20nononono
11111111112014-04-291002014-05-202015-07-202016-07-20nononono
11111111112014-10-22602014-05-202015-07-202016-07-20nononoyes
11111111112014-11-23602014-05-202015-07-202016-07-20nononoyes
11111111112015-02-12302014-05-202015-07-202016-07-20yesnonoyes
11111111112015-07-18402014-05-202015-07-202016-07-20yesnonoyes
11111111112015-10-20152014-05-202015-07-202016-07-20yesnoyesyes
11111111112016-01-10102014-05-202015-07-202016-07-20noyesyesyes
11111111112016-02-12202014-05-202015-07-202016-07-20noyesyesyes
11111111112016-10-16202014-05-202015-07-202016-07-20noyesnoyes

 

Calculation Rules
previous year amountamount between 2015-01-01 and 2015-12-31
current year amountamount between 2016-01-01 and 2016-12-31
renewal amountamount for "driver date" between renewal1 and renewal2 dates
full amountamount for which driver date is greater than active date

 

Output
accountprevious year amountcurrent year amountrenewal amountfull amount
111111111185.0050.0045.00255.00

 

Could you please advise the SQL for this calculation.

Thanks a lot in advance.

Pavan

1 REPLY
Enthusiast

Re: SQL for aggregation for current year, last year etc...

Below Query works. Let me know if it suits your requirement.

 

create set table tdforum 
( acct integer, driver_date date format 'YYYY-MM-DD', amount integer , active date format 'YYYY-MM-DD', ren1 date format 'YYYY-MM-DD', 
ren2 date format 'YYYY-MM-DD', prev_yr_amt varchar(3),cur_yr_amt varchar(3),ren_amt varchar(3), full_amt varchar(3));
select 
acct,
sum(case when to_char(driver_date,'YYYY')='2015' then amount else 0 end) as prev_yr_amnt,
sum(case when to_Char(driver_date,'YYYY')='2016' then amount else 0 end) as curr_yr_amt,
sum(case when driver_date between ren1 and ren2 then amount else 0 end) as ren_amt,
sum(case when driver_date > active then amount else 0 end) as full_amt
from tdforum
group by acct;

Wiki