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.
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 Type1 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 Type1 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 Payment3 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 |
|
|
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 tblgroup by CustomerID |
|
|
|
|
|