|
zeeshan13
Constraint Violating Yak Guru
347 Posts |
Posted - 2010-02-24 : 16:51:04
|
| Hi All,I have a table called ProdStatus with the following fields.OutletID (nvarchar 50),ProductID (nvarchar 50),Cost (decimal 2 places),Price (decimal 2 places), StartDate (datetime),RevertDate (datetime), Status (nvarchar 50),Where the (status=0 or Status is NULL) I want get the latest record that is per product per outlet per date range (latest StartDate/RevertDate)? How can I do that in a select query. The query should get only one record per product per outlet; this one record should be the latest record based on startdate and revertdate.Here's the example. Lets say following are the records in the table (records are seperated by comma).OutletID, ProductID,Cost,Price, StartDate,RevertDate, Status100,001,1.25,2.00,2010-03-07,2010-04-10,NULL100,001,1.25,2.00,2010-02-05,2010-02-10,NULL100,001,1.25,2.00,2010-01-07,2010-01-10,0The output should get the following record.OutletID, ProductID,Cost,Price, StartDate,RevertDate, Status100,001,1.25,2.00,2010-03-07,2010-04-10,NULLThe query should get only one record per product per outlet; this one record should be the latest record based on startdate and revertdate.How can I do that in my SELECT query?Please suggest.Thanks, |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-02-24 : 17:00:36
|
| [code]select * from(select row_number () over(partition by OutletID ,ProductID order by StartDate desc,RevertDate desc) as seq,*from ProdStatus where status = 0 or Status is NULL) awhere a.seq = 1[/code] |
 |
|