Hi Tom,
Can you please help me with the below:
I have a table like below:
Yr Qtr Mth Sales
2010 1 1 1000
2010 1 2 2000
2010 1 3 2500
2010 2 4 3000
2010 2 5 3500
2010 2 6 4000
2010 3 7 4500
2010 3 8 5000
And I'm trying to get a last monthly value for the qtr for each qtr. Example below:
Yr Qtr Mth Sales expected (last month value for previous qtr)
2010 1 1 1000 0
2010 1 2 2000 0
2010 1 3 2500 0
2010 2 4 3000 2500
2010 2 5 3500 2500
2010 2 6 4000 2500
2010 3 7 4500 4000
2010 3 8 5000 4000
You need to look back a variable number of rows. For example, sorting by year and month, in the 2nd quarter you need to go:
month 4 => 1 row
month 5 => 2 rows
month 6 => 3 rows
The second parameter of lag specifies how many rows you want to look back. So to do the above, all you need is a bit of maths!
Specifically, you can take:
( ( month # - 1 ) modulus 3 ) + 1
Pass the SQL equivalent to lag and you get:
create table t (
Yr int, Qtr int, Mth int, Sales int
);
insert into t values (2010, 1, 1, 1000);
insert into t values (2010, 1, 2, 2000);
insert into t values (2010, 1, 3, 2500);
insert into t values (2010, 2, 4, 3000);
insert into t values (2010, 2, 5, 3500);
insert into t values (2010, 2, 6, 4000);
insert into t values (2010, 3, 7, 4500);
insert into t values (2010, 3, 8, 5000);
select t.*,
mod(mth-1, 3)+1,
lag(sales, mod(mth-1, 3)+1)
over (order by yr, mth) last_sale_prev_qtr
from t
order by yr, mth;
YR QTR MTH SALES MOD(MTH-1,3)+1 LAST_SALE_PREV_QTR
2010 1 1 1000 1 <null>
2010 1 2 2000 2 <null>
2010 1 3 2500 3 <null>
2010 2 4 3000 1 2500
2010 2 5 3500 2 2500
2010 2 6 4000 3 2500
2010 3 7 4500 1 4000
2010 3 8 5000 2 4000