sql - Calculating closing (sums of two views, including the past) -


i trying remaining number of working units each month, of sum between bought number of working unit, , consumed number of working unit.

i tried 2 possibilities, both have flaws :

in first test, created "months" table contains every month , every year, in order show months in final matrix wish create these data. one, closing whenever there consumed working unit, when there not, column "empty", because not last closing.

use otrs_revised select     [customer], case when [year] < 2016 1 else [year] end [year], case when [year] < 2016 0 else [month] end [month], [closing] total, sum([closing])                        on (partition [customer] order [year], [month] rows unbounded preceding) closing         [dbo].[wu_closing_view] [customer] in ('customerlist') group [customer], [year], [month], [closing] union select     '' customer, case when [year] < 2016 1 else [year] end [year], case when [year] < 2016 0 else [month] end [month], '' total, '' sum_bought         [dbo].months     [year] <= 2016 group year, month order customer, year, month 

i tried "month month", below query. works 1 month, can't find way use results each month of year 2016.

select      (select sum(closing) expr1              otrs_revised.dbo.wu_bought_view          (customer 'somecustomer') , (dateadd(year, year - 1900, dateadd(month, month - 1, dateadd(day, 0, 0))) <= dateadd(year, 2016 - 1900, dateadd(month, 5 - 1, dateadd(day, 0, 0))))     group customer)   +  (select     sum(closing) expr1          otrs_revised.dbo.wu_consumed_view      (customer 'somecustomer') , (dateadd(year, year - 1900, dateadd(month, month - 1, dateadd(day, 0, 0))) <= dateadd(year, 2016 - 1900, dateadd(month, 5 - 1, dateadd(day, 0, 0)))) group customer) expr1, [month] otrs_revised.dbo.months group [month] 

    select b.*          ( select     case when [year] < 2016 1 else [year] end [year],   case when [year] < 2016 0 else [month] end [month]             [dbo].months         [year] <= 2016     group year, month     order customer, year, month )  left outer join     (select     [customer], case when [year] < 2016 1 else [year] end [year], case when [year] < 2016 0 else [month] end [month], [closing] total, sum([closing])                            on (partition [customer] order [year], [month] rows unbounded preceding) closing             [dbo].[wu_closing_view]     [customer] in ('customerlist')     group [customer], [year], [month], [closing]) b     on a.month = b.month ) 

in approach when union rows don't have matching months getting removed. since want months not have closing match well, need use left outer join


Comments

Popular posts from this blog

Failed to execute goal org.apache.maven.plugins:maven-surefire-plugin:2.12:test (default-test) on project.Error occurred in starting fork -

windows - Debug iNetMgr.exe unhandle exception System.Management.Automation.CmdletInvocationException -

configurationsection - activeMq-5.13.3 setup configurations for wildfly 10.0.0 -