Lead and Lag (aka Window Clause with Partition)
It is possible to do these types of queries without the lead/lag functions ... here's the trick:
- Construct a query which gives you the current id/date and the next id/date
- Wrap a query around this, join, to get the values
Note: Lead / Lag is considered an OLAP feature
Sample, Desired End Result
Sales results for North America
Current Month Last Month
Bob Smith 53212 43728
Steve Kane 75212 32819
Mary Jones 65361 57827
Fred Tuttle 43992 32782
Ray Rogers 78923 72882
Notes:
Lead/Lag works on many different key (partition) types:
- Integer Ids
- Character
- Date/Time
Gaps in keys, non-consecutive keys are not a problem.
e.g. 200510
200511
200512
200601
200602
Or
9/1/2006
9/4/2006
9/5/2006
9/6/2006
-- This query joins the table to itself, and produces a simply 2 column result
set.
select t1.region_id, t1.fiscal_month_id as fiscal_month_id1,
min(t2.fiscal_month_id) as fiscal_month_id2
from sales_data t1, sales_data t2
where t2.fiscal_month_id > t1.fiscal_month_id and
t2.region_id = t1.region_id and
t2.gross_sales > 150 and t1.region_id = 1023
group by t1.region_id, t1.fiscal_month_id
having min(t2.fiscal_month_id) > t1.fiscal_month_id
go
print ' '
go
print 'Report'
go
--- Finished query
select t1.region_id, t1.fiscal_month_id, t1.gross_sales 'current',
t2.gross_sales 'next period'
from
(
select t1.region_id, t1.fiscal_month_id as fiscal_month_id1,
min(t2.fiscal_month_id) as fiscal_month_id2
from sales_data t1, sales_data t2
where t2.fiscal_month_id > t1.fiscal_month_id and
t2.region_id = t1.region_id and
t2.gross_sales > 150 and t1.region_id = 1023
group by t1.region_id, t1.fiscal_month_id
having min(t2.fiscal_month_id) > t1.fiscal_month_id
) t3,
sales_data t1, sales_data t2
where t1.fiscal_month_id = t3.fiscal_month_id1 and
t2.fiscal_month_id = t3.fiscal_month_id2 and
t1.region_id = t3.region_id and
t2.region_id = t3.region_id
order by 1
go
print ' '
go
print 'Data Sample'
go
select fiscal_month_id, region_id, gross_sales
from sales_data where region_id = 1023
order by fiscal_month_id
go
|
|
