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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
2241 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  
 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.06 seconds. Powered By: Snitz Forums 2000