Query Help - SQL Server Forums

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)

Query Help

Author

Topic

MattHeid
Starting Member

7 Posts

Posted - 2009-01-16 : 13:12:53

Can someone give me a hand with this one...? Please

Here is the data that I have...
Customer Invoice OrderDate
100011 748616 2007-01-03
100011 884794 2007-10-04
100011 897271 2007-10-25
100011 905027 2007-11-16
100011 953191 2008-02-12
100011 87023 2008-09-03
100011 165175 2008-12-20
61737 758960 2007-01-26
61737 884921 2007-10-04
61737 918280 2007-12-06
61737 970920 2008-03-10
61737 72465 2008-08-11
61737 135545 2008-11-13

And this is what I want to get after update.
Customer Invoice OrderDate PreviousOrderDate
100011 748616 2007-01-03 NULL
100011 884794 2007-10-04 2007-01-03
100011 897271 2007-10-25 2007-10-04
100011 905027 2007-11-16 2007-10-25
100011 953191 2008-02-12 2007-11-16
100011 87023 2008-09-03 2008-02-12
100011 165175 2008-12-20 2008-09-03
61737 758960 2007-01-26 NULL
61737 884921 2007-10-04 2007-01-26
61737 918280 2007-12-06 2007-10-04
61737 970920 2008-03-10 2007-12-06
61737 72465 2008-08-11 2008-03-10
61737 135545 2008-11-13 2008-08-11

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-16 : 13:18:29

[code]
UPDATE t
SET t.PreviousOrderDate=p.OrderDate
FROM Table t
CROSS APPLY (SELECT TOP 1 OrderDate
FROM Table
WHERE Customer=t.Customer
AND OrderDate<t.OrderDate
ORDER BY OrderDate DESC)p
[/code]