SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 pls help, Select--with history and Runing tot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

srujanavinnakota
Starting Member

34 Posts

Posted - 08/15/2013 :  17:08:32  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 08/16/2013 :  00:54:26  Show Profile  Reply with Quote
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

India
20 Posts

Posted - 08/28/2013 :  10:16:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000