Need to write an SQL query as per the below data.

Database

Need to write an SQL query as per the below data.

Hi Friends,

i have two questions can you please find in below and respond some one please.

Question 1:

Source Data:

2015 2000

2015 3000

2014 2000

2014 1000

OUTPUT :

2015  5000 2014 3000

2014  3000 2012  NULL

In the above data coming from source i need to output as shown in above, can you please some one help me to write an sql query.

Question2:

source data:

2014  NOV  3000

2014  Dec    1000

2015   Jan  2000

2015  FEB  1000

2015  MAR  2000

2015  APR   NULL

For the above data whereever NULL values is  found in the source data it has to check last 6 transctions amounts whch ever the value is there that has to update it.

Can you please some has t respond on the it would help ful me.

2 REPLIES

Re: Need to write an SQL query as per the below data.

create volatile table vt_data
(
yr smallint,
val smallint
)on commit preserve rows;

insert into vt_data values(2015,2000);
insert into vt_data values(2015,3000);
insert into vt_data values(2014,2000);
insert into vt_data values(2014,1000);

create volatile table vt_years
(
yr smallint
)on commit preserve rows;

insert into vt_years values(2012);
insert into vt_years values(2013);
insert into vt_years values(2014);
insert into vt_years values(2015);

Output:
select yrs.yr,sum(val) from
vt_years yrs
left join
vt_data dat
on yrs.yr=dat.yr
group by 1

For the first question why is 2014 required twice?

Or does the output need to be

2015 5000

2014 3000

2013 NULL

2012 NULL?

If so then u can get the result by creating a lookup of the years needed and joining with the source table:

I did that as the sql attached:

N/A

Re: Need to write an SQL query as per the below data.

In both cases you can utilize LAST_VALUE:

select
source, sum(data), source-1,
last_value(sum(data))
over (order by source
rows between 1 preceding and 1 preceding)
from tab
group by source

select source_year, source_month,
last_value(data ignore nulls)
over (order by source_year, source_month
rows 5 preceding)
from tab