how should I get last sold cust and its date

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)

how should I get last sold cust and its date

Author

Topic

imranabdulaziz
Yak Posting Veteran

83 Posts

Posted - 2009-07-18 : 03:19:25

Dear All ,
I am using sql server 2005.
I basically want to find out which item is not sold during specified date range and its last sold customer and date.

My table structure is
item customer date
itemA abc 22-05-09
ItemA def 23-05-09
itemB abc 25-05-09
itemB def 23-05-09
itemB def 24-05-09

Now when I search on 24-05-09 date which Item is not sold
I should get ItemA for 23-05-09 for def.
Same way if search date is 27-05-09. I should get
itemA - def – 23-05-09
itemB - abc - 25-05-09.

I am able to get all product that are not sold during the range that is sold prod – all product = missed product

Please suggest how should I get last sold cust and its date
Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-18 : 03:23:22

SELECT Item, Customer, Date
FROM (
SELECT Item, Customer, Date, ROW_NUMBER() OVER (PARTITION BY Item ORDER BY Date DESC) AS recID
FROM Table1
WHERE Date <= '20090524'
) AS d
WHERE recID = 1

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources