Help with a SELECT query - 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)

Help with a SELECT query

Author

Topic

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, Status
100,001,1.25,2.00,2010-03-07,2010-04-10,NULL
100,001,1.25,2.00,2010-02-05,2010-02-10,NULL
100,001,1.25,2.00,2010-01-07,2010-01-10,0

The output should get the following record.

OutletID, ProductID,Cost,Price, StartDate,RevertDate, Status
100,001,1.25,2.00,2010-03-07,2010-04-10,NULL

The 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
) a
where a.seq = 1[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources