Calculate remaining - 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)

Calculate remaining

Author

Topic

sn34
Starting Member

36 Posts

Posted - 2010-10-15 : 02:35:55

Hello

I have following table named "Sales":
------
id------Doc_type------amount------remiaining_amount <---- i whant to calculate this

1------invoice------400--------------- 0 <--- this 0 because invoice is 400 and sum of next payments(2 and 4) is 250+150=400, remaining=400-400

2------payment------250---------------0

3------invoice------100---------------50 <--- remaining is 50 because 1 invoice is 0 and next payment is 50 and remaining=100-50

4------payment------150--------------0

5------payment------50---------------0

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-10-15 : 03:52:00

seem to have problem in the design of the table

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-10-15 : 05:11:14

[code]
declare @sales table
(
id int,
doc_type varchar(10),
amount int
)

insert into @sales (id, doc_type, amount)
select 1, 'invoice', 400 union all
select 2, 'payment', 250 union all
select 3, 'invoice', 100 union all
select 4, 'payment', 150 union all
select 5, 'payment', 50

select s.id, s.doc_type, s.amount,
remaining_amt = case
when doc_type = 'invoice' then
case
when s.amount + isnull(i.inv_amt, 0) < p.amount
then 0
when s.amount + isnull(i.inv_amt, 0) - p.amount < s.amount
then s.amount + isnull(i.inv_amt, 0) - p.amount
else s.amount
end
else 0
end
from @sales s
cross apply
(
select amount = sum(amount)
from @sales x
where doc_type = 'payment'
) p
cross apply
(
select inv_amt = sum(amount)
from @sales x
where x.id < s.id
and doc_type = 'invoice'
) i

/*
id doc_type amount remaining_amt
----------- ---------- ----------- -------------
1 invoice 400 0
2 payment 250 0
3 invoice 100 50
4 payment 150 0
5 payment 50 0

(5 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

sn34
Starting Member

36 Posts

Posted - 2010-10-16 : 11:19:51

Thank you this works fine, i whant to add one column "cust_id" adn calculate remaining for evri customer like that:

/*
id cust_id doc_type amount remaining_amt
------------- ---------- ----------- -------------
1-----1-----invoice----400---------0
2-----1-----payment----250---------0
3-----1-----invoice----100---------50
4-----1-----payment----150---------0
5-----1-----payment----50---------0
6-----2-----invoice----100---------30
7-----2-----payment----20---------0
8-----2-----payment----50---------0

how can i do this?

(5 row(s) affected)
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-10-16 : 11:28:07

[code]
from @sales s
cross apply
(
select amount = sum(amount)
from @sales x
where doc_type = 'payment'
and x.cust_id = s.cust_id
) p
cross apply
(
select inv_amt = sum(amount)
from @sales x
where x.id < s.id
and doc_type = 'invoice'
and x.cust_id = s.cust_id
) i
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sn34
Starting Member

36 Posts

Posted - 2010-10-16 : 11:56:57

Thank you so much and in my database there are data like that:

1-----1-----invoice----400---------0
2-----1-----invoice----( - 400)---------0
3-----1-----payment---(-250)---------0
4-----1-----payment----250---------0

I whant to filter it where invoice' amount is possitive and where payment's amount is negative and after that calculate remaining, like that:
1-----1-----invoice----400---------0
3-----1-----payment---(-250)---------0

can you help me?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources