Subtract totals in diff rows within same table

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

General SQL Server Forums

New to SQL Server Programming

Subtract totals in diff rows within same table

Author

Topic

9four9
Starting Member

1 Post

Posted - 2011-05-20 : 00:30:13

I need your expertise, and I hope this is a relatively easy solution.

My mission is to identify any records that do not balance—in other words, it doesn’t equal zero. I need to find a way to perform the following:

1. Capture a total from one field in one row, and then subtract it from a different field in the next row.
2. Use a condition that ignores any row that has a “TX_Start” of $0.00
3. Product a result set based on hundred of rows, so the solution should hopefully not be convoluted and lengthy

NOTE: All the data is in the same table.

For example, I have the following data:

TX_ID TX_Start TX_End
----------------------------------------------
1 1234.00 1233.00
2 1233.00 1232.00
3 1232.00 1231.00
4 1231.00 1230.00
5 0.00 1229.00
6 1225.00 1224.00

I need to take the “TX_End” for TX_ID 1, and subtract it from “TX_Start” in TX_ID 2. In other words, I want to take 1233.00 from the first record, and then subtract it from 1233.00 from the second record. And, of course, I have to repeat this process throughout.

Based on the example above, I intend to get the following result set:

TX_ID TX_Balance
-------------------
2 0
3 0
4 0
6 (4.00)

The query for the initial results is the simple part that even I can do with my limited knowledge.

select tx_id, tx_start, tx_end from transactions
where tx_start <> 0
order by tx_id

However, I don’t necessarily know how to subtract the TX_End field in the previous record from the TX_Start in the next record.

Do I need to use a #temp table to dump one of the fields, and then use a join to accurately subtract the table?

Any help you can provide with be greatly appreciated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-20 : 06:56:42

Query below will work only on SQL 2005 or higher. Assumes continuous TX_IDs.


select
   b.TX_ID,
   b.TX_Start-a.TX_End
from
   YourTable a
   cross apply
   (
      select b.TX_ID, case when TX_Start = 0 then a.TX_End else b.TX_Start end as TX_Start
      from YourTable b
      where b.TX_ID = a.TX_ID+1  and b.TX_Start <> 0
   )b

It parses without error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources