I need to calculate the value of indebtedness column so that when openingBalance !=0 then indebtedness = openingBalnce+SalesTotal-SalesReturn. But, when openingBalnce = 0 then indebtedness = indebtedness of the previous monthSales with the same SalesID. If the previous value = 0 get the previous value and continue get previous value till have value in this column:
我需要计算indebtedness列的值,以便在opensBalance!= 0时,然后indebtedness = openingBalnce + SalesTotal-SalesReturn。但是,当opensBalnce = 0时,indebtedness =上一个月的销售额具有相同SalesID的销售额。如果前一个值= 0,则获取前一个值并继续获取上一个值,直到在此列中具有值:
SalesMonth SalesID openingBalance SalesTotal SalesReturn Indebtednes
---------- ------- -------------- ---------- ----------- ------------
1 1 352200 0 5600 Null
------------------------------------------------------------------------
1 2 50000 1100 0 Null
------------------------------------------------------------------------
1 3 9500 6000 0 Null
------------------------------------------------------------------------
2 1 0 0 1200 Null
------------------------------------------------------------------------
2 2 0 300 0 Null
------------------------------------------------------------------------
2 3 0 500 1000 Null
------------------------------------------------------------------------
3 1 0 600 0 NULL
------------------------------------------------------------------------
3 2 0 200 0 NULL
-----------------------------------------------------------------------
3 3 0 0 10 NULL
-----------------------------------------------------------------------
.
.
.
12 1 0 0 0 NULL
----------------------------------------------------------------------
12 2 0 0 0 NULL
----------------------------------------------------------------------
12 3 0 0 0 NULL when openingBalance !=0 then Indebtednes=openingBalnce+SalesTotal-SalesReturn
when openingBalnce =0 then Indebtednes=Indebtednes (of the previous
month of the same SalesID)+SalesTotal-SalesReturn.
And this is the output i want.
这是我想要的输出。
SalesMonth SalesID openingBalance SalesTotal SalesReturn Indebtednes
---------- ------- -------------- ---------- ----------- ------------
1 1 352200 0 5600 346600
------------------------------------------------------------------------
1 2 50000 1100 0 51100
------------------------------------------------------------------------
1 3 9500 6000 0 15500
------------------------------------------------------------------------
2 1 0 0 1200 345400
------------------------------------------------------------------------
2 2 0 300 0 51400
------------------------------------------------------------------------
2 3 0 500 1000 15000
------------------------------------------------------------------------
3 1 0 600 0 346000
------------------------------------------------------------------------
3 2 0 200 0 51600
-----------------------------------------------------------------------
3 3 0 0 10 14990
-----------------------------------------------------------------------
.
.
.
12 1 0 0 0 NULL
----------------------------------------------------------------------
12 2 0 0 0 NULL
----------------------------------------------------------------------
12 3 0 0 0 NULL1 个解决方案
#1
you could try like below cte query
你可以尝试下面的cte查询
declare @tb table(SalesMonth int,SalesID int,
openingBalance money,SalesTotal money,SalesReturn money,Indebtednes money)
insert into @tb(SalesMonth,SalesID,openingBalance,SalesTotal,SalesReturn,Indebtednes)
values (1,1,352200,0,5600,Null)
,(1,2,50000,1100,0,Null)
,(1,3,9500,6000,0,Null)
,(2,1,0,0,1200,Null)
,(2,2,0,300,0,Null)
,(2,3,0,500,1000,Null)
,(3,1,0,600,0,NULL)
,(3,2,0,200,0,NULL)
,(3,3,0,0,10,NULL)
;with t1 as (select *, row_number() over
(order by salesid,SalesMonth) as rno from @tb),
t2(inde,rno,salid,mnth)as
(select case when openingBalance !=0 then
openingBalance+SalesTotal-SalesReturn
when openingBalance =0 then 0 end as inde,
rno,SalesID,SalesMonth from t1 where rno=1
union all
select case when openingBalance !=0 then
openingBalance+SalesTotal-SalesReturn
when openingBalance =0 then
case when SalesID=salid then inde+SalesTotal-SalesReturn
else 0 end end,
t1.rno,t1.SalesID,SalesMonth from t2 join t1 on t2.rno+1=t1.rno )
select SalesMonth,SalesID,openingBalance,
SalesTotal,SalesReturn,inde as Indebtednes from t1
inner join t2 on t1.SalesID=t2.salid and
t1.SalesMonth=t2.mnth order by mnth,salid
#1
you could try like below cte query
你可以尝试下面的cte查询
declare @tb table(SalesMonth int,SalesID int,
openingBalance money,SalesTotal money,SalesReturn money,Indebtednes money)
insert into @tb(SalesMonth,SalesID,openingBalance,SalesTotal,SalesReturn,Indebtednes)
values (1,1,352200,0,5600,Null)
,(1,2,50000,1100,0,Null)
,(1,3,9500,6000,0,Null)
,(2,1,0,0,1200,Null)
,(2,2,0,300,0,Null)
,(2,3,0,500,1000,Null)
,(3,1,0,600,0,NULL)
,(3,2,0,200,0,NULL)
,(3,3,0,0,10,NULL)
;with t1 as (select *, row_number() over
(order by salesid,SalesMonth) as rno from @tb),
t2(inde,rno,salid,mnth)as
(select case when openingBalance !=0 then
openingBalance+SalesTotal-SalesReturn
when openingBalance =0 then 0 end as inde,
rno,SalesID,SalesMonth from t1 where rno=1
union all
select case when openingBalance !=0 then
openingBalance+SalesTotal-SalesReturn
when openingBalance =0 then
case when SalesID=salid then inde+SalesTotal-SalesReturn
else 0 end end,
t1.rno,t1.SalesID,SalesMonth from t2 join t1 on t2.rno+1=t1.rno )
select SalesMonth,SalesID,openingBalance,
SalesTotal,SalesReturn,inde as Indebtednes from t1
inner join t2 on t1.SalesID=t2.salid and
t1.SalesMonth=t2.mnth order by mnth,salid