mysql - Balance Trend Analysis -

i looking running percentage change each month of balances product. month field representative of last business day of each month.

i have tried using common table expression below:

declare @tab table  ( date datetime,  balance decimal (38,8),  pdct_type_c varchar (5),  excl_c varchar (1)  )  insert @tab  select  date,  sum(balance), pdct_type_c, rc_excl_c  eric..rc_xpos  rc_excl_c null , pdct_type_c = 'hln'  group xpos_d,pdct_type_c,rc_excl_c  ;  cte  (  select  row_number() on (order pdct_type_c,date) rn,  date,  balance,  pdct_type_c  @tab  )  select  date,  balance,  (((select balance cte cte.rn=1))-(select balance cte cte.rn=t1.rn+1)) 'ead mom difference',  (((select balance cte cte.rn=1))/(select balance cte cte.rn=t1.rn+1)) 'ead mom % difference',  pdct_type_c  cte t1 

this works fine if there 1 product, problem though if there many products calculates percentage change on row before- might different product when there multiple product types.

does know how solve this?

i'm hoping results below. have balance,date , product type fields available hoping calculate balance difference , balance change without having row partition , calculation based on row above.

please let me know if have questions.

desired result:

date        | balance      | balance difference | balance % change |  product type --------------------------------------------------------------- 30/06/2017  | 4,000,000.00 | --                 | --               | ap 30/06/2017  | 2,000,000.00 | --                 | --               | za 30/06/2017  | 1,000,000.00 | --                 | --               | lp 31/07/2017  | 5,000,000.00 |1,000,000.00        | 0.25             | ap             31/07/2017  | 2,000,000.00 |1,000,000.00        | 0.5              | lp  31/07/2017  | 1,000,000.00 |-1,000,000.00       | -0.5             | za 

is you're looking for?

if object_id('tempdb..#testdata', 'u') not null  drop table #testdata;  create table #testdata (     [date] date not null,     balance money not null,     producttype char(2) not null      );  insert #testdata (date, balance, producttype) values     ('2017-06-30', 4000000, 'ap'),     ('2017-06-30', 2000000, 'za'),     ('2017-06-30', 1000000, 'lp'),      ('2017-07-31', 5000000, 'ap'),     ('2017-07-31', 1000000, 'za'),     ('2017-07-31', 2000000, 'lp');  select * #testdata td;  --==================================================  select      td.date,      td.balance,      [ead mom difference] = td.balance - lag(td.balance, 1) on (partition td.producttype order td.date),     [ead mom % difference] = td.balance / lag(td.balance, 1) on (partition td.producttype order td.date) - 1,     td.producttype     #testdata td order     td.date,     td.producttype;  --================================================== --================================================== -- sql server (pre 2012) version...      cte_testdatarn (         select              td.date, td.balance, td.producttype,             rn = row_number() on (partition td.producttype order td.date)                     #testdata td         ) select      tdr1.date,     tdr1.balance,     [ead mom difference] = tdr1.balance - tdr2.balance,     [ead mom % difference] = tdr1.balance / tdr2.balance - 1,     tdr1.producttype     cte_testdatarn tdr1     left join cte_testdatarn tdr2         on tdr1.producttype = tdr2.producttype         , tdr1.rn = tdr2.rn + 1 order      tdr1.date,     tdr2.producttype;