Compare Previous Row to Current Row Field Value

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 2005 Forums

Transact-SQL (2005)

Compare Previous Row to Current Row Field Value

Author

Topic

rosshughes
Starting Member

16 Posts

Posted - 2009-12-30 : 14:33:44

Hi,

I am creating a stored procedure that creats/sorts a dataset by part number, due date.

There is an onhand quantity field and a shipping requirement field in each row.

I would like to have it do this: if the previous row has the same part number then take the current row onhand quantity and subtract the previous row shipping requirement. If the previous row does not have the same part number then just leave the value as it is.

I need to create this data in the stored procedure because at the report design level it can be sorted many different ways.

Thanks for any ideas,
Ross

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-30 : 14:49:10

Please give table structure(s) and some sample data with expected output. I assume that the table(s) has a clustered index.

Thanks,

Jim

Everyday I learn something that somebody else already knew

rosshughes
Starting Member

16 Posts

Posted - 2009-12-30 : 15:25:49

Thanks for the reply. Here is what I would like the results to look like.

The last column should be a running total calculated field:

count partnum ship onhand calconhand
1 10647 400.00 582 582
2 10803 1116.00 1116 1116
3 13135 1000.00 0 0
4 13141 1000.00 0 0
5 13791 50.00 0 0
6 13794 100.00 19 19
7 14000 25.00 10 10
8 14283 25.00 4 4
9 14640 175.00 0 0
10 14680 239.00 0 0
11 14680 206.00 0 -239
12 14680 206.00 0 -445
13 14680 206.00 0 -651
14 14764 1000.00 1064 1064
15 16058 165.00 473 473

The relevant part in this data is '14680'. It starts out with 0 and is deducted as the requirements are added and ends up with -651. The single parts in the dataset are just showing current onhand. I have only included the standard onhand as a reference. In the final data I will eliminate that and only include the calculated onhand column.

Thanks again for looking at this!
Ross

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-02 : 02:42:16

something like this


SELECT t.count,t.partnum,t.ship,t.onhand,
t.onhand-COALESCE(t1.TotalShip,0) AS  calconhand
FROM Yourtable t
OUTER APPLY (SELECT SUM(Ship) AS TotalShip
             FROM Yourtable
             WHERE partnum=t.partnum
             AND count < t.count)t1

rosshughes
Starting Member

16 Posts

Posted - 2010-01-03 : 23:27:29

Great! Thanks a lot, visakh16.

That works perfectly.

I really appreciate your taking the time to help me.

Ross

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-04 : 02:58:19

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources