Running Total query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Running Total query

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-12 : 16:29:18

[code]
/*
--I am having some issues with a query wher I need to do the following without a cursor or a looping clause. I want to take the AMount in

As illustrated below I have two tables. #PaymentItems is my table that stores a payment and it joins the invoiceItems table using invoiceID field.

I need to apply the payment to each line Item until the amount is used up. If there is money left over it goes into the amount Remaining column in PaymentItems

Here's the sql..
*/

Create Table #InvoiceItems(InvoiceID int, Rate decimal 5,2),PaidAmount Decimal(5,2))

insert into #InvoiceItems(InvoiceID, Rate)
select 1,10.00
Union All
select 1,11.00
Union All
select 1,12.00
Union All
select 1,14.00
Union
select 2,10.00
Union All
select 2,11.00
Union All
select 2,12.00
Union All
select 2,14.00

Create Table #PaymentItems(PaymentID int,InvoiceID int,Amount decimal(5,2),AmountRemaining decimal(5,2))

Insert into #PaymentItems(PaymentID,InvoiceID,Amount)
select 31321,1,80
Union all
select 31322,2,18

/*
My desired results would be this for :

InvoiceITems
INvoiceID|Rate|PaidAmount
1,10,10
1,11,11
1,12,12
1,13,13
1,14,14
2,10,10
2,11,8
2,12,0
2,13,0
2,14,0

see how on invoice 2 only $18 could be applied so after the (10 and 8) were applied the remaing items were 0 because there was nothing left of the payment.

and this for :

PaymentItems
PaymentID|InvoiceID|Amount|AmountRemaining
31321,1,80,33
31322,2,18,0

Here you can see that there was $33 left from the $80 on payment 1, but nothing left of the $18 on payment 2.

--I wrote the following query but it is not working

Select *,Row_Number() Over (Partition By InvoiceID Order by Rate Desc) as RowID
into #TmpInvoiceItems
from #INvoiceITems

Update a
Set a.Paidamount = case
When b.Amount - (Select Sum(coalesce(PaidAmount,0)) from #TmpInvoiceItems aa Where aa.InvoiceID = a.InvoiceID and aa.RowID <= a.RowID) > a.Rate then a.Rate
else b.Amount - (Select Sum(coalesce(PaidAmount,0)) from #TmpInvoiceItems aa Where aa.InvoiceID = a.InvoiceID and aa.RowID <= a.RowID)
end
From #TmpInvoiceItems a
Inner Join #PaymentItems b
on a.InvoiceID = b.InvoiceID

Update a
set AmountRemaining = a.Amount - (Select Sum(coalesce(PaidAmount,0)) from #TmpInvoiceItems aa Where aa.InvoiceID = a.InvoiceID)
from #PaymentItems a

--(The case statment is not evaluating properly). Any sugestions.
*/

[/code]

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-13 : 15:47:40

It's working using the following code. Any sugestions on ways to improve would be appriciated.


drop table #InvoiceItems
Drop Table #PaymentItems
Drop Table #TmpInvoiceItems

Create Table #InvoiceItems(InvoiceID int, Rate decimal (5,2),PaidAmount Decimal(5,2))

insert into #InvoiceItems(InvoiceID, Rate)
select 1,10.00
Union All
select 1,11.00
Union All
select 1,12.00
Union All
select 1,14.00
Union
select 2,10.00
Union All
select 2,11.00
Union All
select 2,12.00
Union All
select 2,14.00


Create Table #PaymentItems(PaymentID int,InvoiceID int,Amount decimal(5,2),AmountRemaining decimal(5,2))

Insert into #PaymentItems(PaymentID,InvoiceID,Amount) 
select 31321,1,80
Union all
select 31322,2,18

select * 
into #TmpInvoiceItems
From
(
Select *,Row_Number() Over (Partition By InvoiceID Order by Rate Desc) as RowID
from #INvoiceITems
) a
order by a.InvoiceID,a.RowiD


update a
Set a.Paidamount = case 
				when b.Amount >= coalesce((Select Sum(Rate) from #TmpInvoiceItems aa Where aa.InvoiceID =  a.InvoiceID and aa.RowID <= a.RowID),0) then a.Rate
				when b.amount - coalesce((Select Sum(Rate) from #TmpInvoiceItems aa Where aa.InvoiceID =  a.InvoiceID and aa.RowID < a.RowID),0) > 0 then b.amount - coalesce((Select Sum(Rate) from #TmpInvoiceItems aa Where aa.InvoiceID =  a.InvoiceID and aa.RowID < a.RowID),0)
				else 0
				End
From #TmpInvoiceItems a
Inner Join #PaymentItems b
on a.InvoiceID = b.InvoiceID


Update a
set AmountRemaining = a.Amount - (Select Sum(coalesce(PaidAmount,0)) from #TmpInvoiceItems aa Where aa.InvoiceID =  a.InvoiceID)
from #PaymentItems a

select * from
#TmpInvoiceItems a
Inner Join #PaymentItems b
on a.InvoiceID = b.InvoiceID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources