sql server - SQL query multiplying SUM results when joining to multiple transaction tables -


i'm developing report measures cash performance based on allocation , cancellation dates. measure i've queried transactions sub query pulls transactions , joined on account number , transaction , dates. summed @ top of query.

all works fine when using 1 sub query i'm having add transactions sub query; 1 calculate actual payments allocation date (to cancellation date or today if value null) , 2nd calculate cancelled payments allocation date + 14 days (to cancellation date or today if value null).

the first sub query works i've added second it's multiplying summed value 8. i've had in past when trying query table twice. (not 8). ideas?

thanks

the sums totalling these (test1 works fine, test2 not much) , sub queries:

sum(case when v1.[transaction date1] >= dateadd(dd, 1, eomonth(getdate(), -1))              v1.[payments] else 0      end) test1, sum(case when v2.[transaction date2] >= dateadd(dd, 1, eomonth(getdate(), -1))              v2.[payments2] else 0      end) test2,

my code:

left join      (select           [account number1], [transaction date1], sum([payments1]) [payments]                (select               accountno [account number1],               trandt [transaction date1],               case                   when transactiontype = 'credit'                      tranamt * -1                     else tranamt               end [payments1]                          transactions t (nolock)           join                transactiondetails td (nolock) on td.transactionid = t.transactions1           join                transactions (nolock) on a.id = transactions1                                            , [trantype] '%pay%'                                            , [tranamt] <> 0           inner join                accounts na (nolock) on a.accountid = accounts1                          transactiontype = 'credit') v      group           [account number1], [transaction date1]) v1 on maint.[accno] = v1.[account number1]                                                     , maint.[allocation date] <= v1.[transaction date1]                                                     , v1.[transaction date1] <=  maint.[transaction date] left join      (select           [account number2], [transaction date2], sum([payments2]) [payments2]                (select               accountno [account number2],               trandt [transaction date2],               case                    when transactiontype = 'credit'                      tranamt * -1                     else tranamt               end [payments2]                          transactions t (nolock)           inner join                transactiondetails td (nolock) on td.transactionid = t.transactions1           inner join                transactions (nolock) on a.id = transactions1                                            , [trantype] '%pay%'                                            , [tranamt] <> 0           inner join                accounts na (nolock) on a.accountid = accounts1                          transactiontype = 'debit') v       group            [account number2], [transaction date2]) v2 on maint.[accno] = v2.[account number2]                                                      , dateadd(dd,10,maint.[dca allocation date]) <= v2.[transaction date2]                                                      , v2.[transaction date2] <=  maint.[transaction date]

since inner subselect same in both joins, should put in statement @ top , use twice. this:

with v_cte   (select               accountno [account number2],               trandt [transaction date2],               case                    when transactiontype = 'credit'                      tranamt * -1                     else tranamt               end [payments2]                          transactions t (nolock)           inner join                transactiondetails td (nolock) on td.transactionid = t.transactions1           inner join                transactions (nolock) on a.id = transactions1                                            , [trantype] '%pay%'                                            , [tranamt] <> 0           inner join                accounts na (nolock) on a.accountid = accounts1                          transactiontype = 'debit')  ... left join      (select           [account number1], [transaction date1], sum([payments1]) [payments]                v_cte v      group           [account number1], [transaction date1]) v1 on maint.[accno] = v1.[account number1]                                                     , maint.[allocation date] <= v1.[transaction date1]                                                     , v1.[transaction date1] <=  maint.[transaction date] left join      (select           [account number2], [transaction date2], sum([payments2]) [payments2]                v_cte v       group            [account number2], [transaction date2]) v2 on maint.[accno] = v2.[account number2]                                                      , dateadd(dd,10,maint.[dca allocation date]) <= v2.[transaction date2]                                                      , v2.[transaction date2] <=  maint.[transaction date]

Comments

Post a Comment

Popular posts from this blog

python - Selenium remoteWebDriver (& SauceLabs) Firefox moseMoveTo action exception -

while performing simple hover test on firefox through selenium (3.4.0) python bindings (3.4.3) using of available os in saucelabs (except linux, latest available firefox version old), performing following actionchain: hover = actionchains(driver) hover.move_to_element(elm_men_menu).perform() it throws following error: taceback: file [...] in testhover hover.perform() file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/common/action_chains.py", line 80, in perform action() file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/common/action_chains.py", line 290, in <lambda> command.move_to, {'element': to_element.id})) file "/usr/local/lib/python2.7/dist-packages/selenium/webdriver/remote/webdriver.py", line 256, in execute self.error_handler.check_response(response) file "/usr/local/lib/python2.7/...
Read more

html - How to custom Bootstrap grid height? -

why bootstrap grid has height 591.067 ? did height came ? .main-post { padding: 20px; background-color: #fff; border: 1px solid #d8d8d8; margin-bottom: 100px; } .main-post .post-categories { margin-bottom: 10px } .main-post h3 { margin: 0 0 10px; color: #777; letter-spacing: -1px; font-weight: bold } .main-post .post-author, .main-post .post-date, .main-post .post-comments { font-size: 12px } .main-post img { display: block; margin: 10px 0; } .main-post .post-summary { line-height: 1.7; color: #888 } .main-post { color: #999 } <div class="col-sm-6"> <div class="main-post"> <h3 class="post-title"> <a href="#"> post title </a> </...
Read more

transpose - Maple isnt executing function but prints function term -

im using maple im in worksheetmode tried using maple input , 2d input , want transpose matrix a: := `<|>`(`<,>`(1, .5, -2), `<,>`(.5, 9/4+b, 5+3*b), `<,>`(-2, 5+3*b, 18+9*b+4*a)); b:= transpose(a); when exectute sheet dont translated values, there same input. matrix looks same input matrix plus function term. you can see picture in following link: why arent functions executed? meanwhile b:=a^+ doing right way , transposed matrix. other functions return function body instead needed values... if using 2d input mode (the default) space you've got between transpose , bracketed (b) interpreted multiplcation. rid of such space. also, either load package @ start of document like, with(linearalgebra): before calling transpose command package, or call it's full name like, linearalgebra:-transpose(b);
Read more