oracle - How to calculate price change over 3 years in SQL query -
i need calculate price change of item (both in cost , % change) on last 3 years.
the table has 4 fields:
sku_no, date_updated, price, active_flag
when active_flag field a, item active, when i inactive. items haven't changed prices in years won't have 3 years of entries inactive flag.
sample table
sku_no update_date price active_flag 30 1/1/1999 40.8 33 1/1/2014 70.59 33 1/1/2013 67.23 33 1/1/2012 60.03 33 1/1/2011 55.08 33 1/1/2010 55.08 34 1/1/2009 51 36 1/1/2014 70.59 36 1/1/2013 67.23 36 1/1/2012 60.03 38 1/1/2002 43.32 38 1/1/2001 43.32 38 4/8/2000 43.32 38 1/1/1999 43.32 39 1/1/2014 73.08 39 1/1/2013 69.6 39 1/1/2012 62.13 39 1/1/2011 57 39 1/1/2010 57 39 1/1/2009 52.8 this first query wrote. i'm not familiar complex calculations
select s.vendor, s.fiscal_year, s.fiscal_month_no, s.fiscal_year||'_'||fiscal_month_no period, case when s.cost_used_flag in ('contract') 'contract' else 'non-contract' end contract_type, case when ((s.fiscal_year = 2014 , fiscal_month_no <=9) or (fiscal_year = 2013 , fiscal_month_no >=10)) 'cp_1' when ((s.fiscal_year = 2013 , fiscal_month_no <= 9) or (fiscal_year = 2012 , fiscal_month_no >=10)) 'cp_2' when ((s.fiscal_year = 2012 , fiscal_month_no <= 9) or (fiscal_year = 2011 , fiscal_month_no >=10)) 'cp_3' else 'null' end cagr_periods, case when s.market in ('po', 'sc', 'oc') 'pc' else 'ec' end market_type, s.market, s.cost_plus_flag, s.cost_used_flag, lpad(s.pc_item_no,6,'0') new_item_no, s.pc_item_no, i.item_no, i.vend_cat_num, i.description, s.pc_prod_cat, s.pc_prod_subcat, i.sell_uom, i.qty_per_sell_uom, i.primary_uom, i.head_conv_fact, sum(s.qty_each) quantity_sold, sum(s.ext_gross_cogs) total_cogs, sum(s.ext_gross_cogs)/ sum(s.qty_each) net_sales, sum(s.ext_sales)/ sum(s.qty_each) asp, sum(s.ext_sales) total_sales, sum(s.ext_sales) - sum(s.ext_gross_cogs) gross_profit sixsigma.cia_all_sales_trend_data s inner join mgmsh.item on s.pc_item_no = i.item_no s.vendor = 'bd' , (s.ext_sales not null , s.fiscal_year in ('2013','2012','2011')) group s.vendor, s.fiscal_year, s.fiscal_month_no, s.fiscal_year||'_'||fiscal_month_no, case when s.market in ('po', 'sc', 'oc') 'pc' else 'ec' end, case when s.cost_used_flag in ('contract') 'contract' else 'non-contract' end, case when ((s.fiscal_year = 2014 , fiscal_month_no <=9) or (fiscal_year = 2013 , fiscal_month_no >=10)) 'cp_1' when ((s.fiscal_year = 2013 , fiscal_month_no <= 9) or (fiscal_year = 2012 , fiscal_month_no >=10)) 'cp_2' when ((s.fiscal_year = 2012 , fiscal_month_no <= 9) or (fiscal_year = 2011 , fiscal_month_no >=10)) 'cp_3' else 'null' end, s.market, s.cost_used_flag, s.cost_plus_flag, s.pc_item_no, s.pc_prod_cat, i.sell_uom, i.qty_per_sell_uom, i.primary_uom, i.head_conv_fact, i.description, i.vend_cat_num, s.pc_prod_subcat, i.item_no order s.pc_item_no,s.fiscal_year, s.fiscal_month_no
there several ways approach this, recommend windowing function such lag or lead. these functions, can reference neighboring rows. example:
lead(column, offset, default) on (partition some_column order column) and in example below:
lead(price, 1, price) on (partition sku_no order update_date desc) here working example sample data:
with sample_data ( select '30' sku_no, to_date('1/1/1999','dd/mm/yyyy') update_date, 40.8 price, 'i' active_flag dual union select '33', to_date('1/1/2014','dd/mm/yyyy'), 70.59, 'a' dual union select '33', to_date('1/1/2013','dd/mm/yyyy'), 67.23, 'i' dual union select '33', to_date('1/1/2012','dd/mm/yyyy'), 60.03, 'i' dual union select '33', to_date('1/1/2011','dd/mm/yyyy'), 55.08, 'i' dual union select '33', to_date('1/1/2010','dd/mm/yyyy'), 55.08, 'i' dual union select '34', to_date('1/1/2009','dd/mm/yyyy'), 51 , 'a' dual union select '36', to_date('1/1/2014','dd/mm/yyyy'), 70.59, 'a' dual union select '36', to_date('1/1/2013','dd/mm/yyyy'), 67.23, 'i' dual union select '36', to_date('1/1/2012','dd/mm/yyyy'), 60.03, 'i' dual union select '38', to_date('1/1/2002','dd/mm/yyyy'), 43.32, 'a' dual union select '38', to_date('1/1/2001','dd/mm/yyyy'), 43.32, 'i' dual union select '38', to_date('4/8/2000','dd/mm/yyyy'), 43.32, 'i' dual union select '38', to_date('1/1/1999','dd/mm/yyyy'), 43.32, 'i' dual union select '39', to_date('1/1/2014','dd/mm/yyyy'), 73.08, 'a' dual union select '39', to_date('1/1/2013','dd/mm/yyyy'), 69.6 , 'i' dual union select '39', to_date('1/1/2012','dd/mm/yyyy'), 62.13, 'i' dual union select '39', to_date('1/1/2011','dd/mm/yyyy'), 57 , 'i' dual union select '39', to_date('1/1/2010','dd/mm/yyyy'), 57 , 'i' dual union select '39', to_date('1/1/2009','dd/mm/yyyy'), 52.8 , 'i' dual) select sku_no, update_date, price, lead(price,1, price) on (partition sku_no order update_date desc) prior_price, -- showing offset price - lead(price,1, price) on (partition sku_no order update_date desc) price_difference, -- calculate difference round((price - lead(price,1, price) on (partition sku_no order update_date desc)) * 100 /price, 2) percent_change -- calculate percentage sample_data update_date >= add_months(trunc(sysdate,'yyyy'),-36); -- said in last 3 years you can use lag different order sort. if want calculate difference 3 years prior, suggest using keep function.
Comments
Post a Comment