You Asked
G'day
I'm trying to master the use of the lag function to get last years sales within the same query.
Our data is partitioned by two time attributes, fin_year and fin_month.
CREATE TABLE ths_sales_det (state_code VARCHAR2(3) NOT NULL ,fin_year NUMBER NOT NULL,fin_month NUMBER NOT NULL,acc_nbr NUMBER NOT NULL
,locn_code NUMBER NOT NULL,subd_code NUMBER NOT NULL,prod_type_id NUMBER NOT NULL ,sales NUMBER,costs NUMBER,qty_bought NUMBER);
And the typical report needs to be sales grouped by month.
Month Sales Last Year Sales
Jul02 100 50
Aug02 90 30
...
INSERT INTO ths_sales_det VALUES ('WA',2003,1,332,2010,123, 0,100, 70,1,1);
INSERT INTO ths_sales_det VALUES ('WA',2003,2,332,2010,123, 0,90, 70,1,1);
INSERT INTO ths_sales_det VALUES ('WA',2002,1,332,2010,123, 0,50, 70,1,1);
INSERT INTO ths_sales_det VALUES ('WA',2002,2,332,2010,123, 0,30, 70,1,1);
However I am unable to get the query to distinguish between previous row and previous year.
Keep in mind that some months may not necessarily have sales, so you can't offset by 12.
This is obviously incorrect...
select sum(sales), fin_year, fin_month,lag(sum(sales)) over ( order by fin_year,fin_month) last_year_sale
from ths_sales_det
group by fin_year,fin_month
SUM(SALES) FIN_YEAR FIN_MONTH LAST_YEAR_SALES
---------- ---------- ---------- ---------------
50 2002 1
30 2002 2 50
100 2003 1 30
90 2003 2 100
Would you please assist?
Thankyou in advance.
and Tom said...
I think a simple pivot is what you want -- just get the sum of sales by mon/year for the years in question and pivot:
ops$tkyte@ORA920> select fin_month, max(decode( fin_year, 2002, sales, null )) fy_2002,
2 max(decode( fin_year, 2003, sales, null )) fy_2003
3 from (
4 select sum(sales) sales, fin_year, fin_month
5 from ths_sales_det
6 group by fin_year, fin_month
7 )
8 group by fin_month
9 /
FIN_MONTH FY_2002 FY_2003
---------- ---------- ----------
1 50 100
2 30 90
Rating
(21 ratings)
Is this answer out of date? If it is, please let us know via a Comment