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 2008 Forums
 Transact-SQL (2008)
 pls help, Select--with history and Runing tot

Author  Topic 

srujanavinnakota
Starting Member

34 Posts

Posted - 2013-08-15 : 17:08:32
Please help me with Select statement.

I have a table which has transactions like below. Below is like ledger table.

rowNumber Tran_num CustomerID CustItemTypeID Amount Type
1 2 12380047 19745457 5000 Payment
2 3 12380047 0 -545 Invoice
3 5 12380047 19684332 3219 Payment
1 1 12270393 18970681 100 Payment
2 2 12270393 0 -995 Invoice
3 5 12270393 19028057 9234 Payment
4 7 12270393 19124979 6156 Payment


By looking at the history and doing running total, I need to figured, Which CustItemTypeID has made the invoice to 0 or greater than 0



For example, I need the data like below.
rowNumber Tran_num CustomerID CustItemTypeID Amount Balance amountDriventoinvoice Type
1 2 12380047 19745457 5000 5000-545=4455 545 Payment
3 5 12270393 19028057 9234 100-995+9234=8339 895 Payment

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-16 : 00:54:26
Hi Srujana,
what about the remaining records ?
3 5 12380047 19684332 3219 Payment
3 5 12270393 19028057 9234 Payment
4 7 12270393 19124979 6156 Payment

For latest CustomerTypeID only you have posted result or something else logic?
Can you explain the exact output and explanation

--
Chandu
Go to Top of Page

ShivaKrishna
Starting Member

20 Posts

Posted - 2013-08-28 : 10:16:39
select rowNumber, Tran_num, CustomerID, CustItemTypeID ,Amount ,Balance,sum(Amount )+sum(Invoice) as [amountDriventoinvoice], case when sum(Amount)+sum(Invoice) >=0 then 'Payment' else 'Invoice' as Type
from
tbl
group by CustomerID
Go to Top of Page
   

- Advertisement -