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;

Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more