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
Post a Comment