Solved

Select Last Sale Date

Posted on 2011-03-14
3
450 Views
Last Modified: 2012-08-13
Hello,

I have a SaleTable with upc, movement date, store, vendor and qnty as part of the record.  I have InvTable with  upc, store, and vendor as part of the record.  I need to return the top n records (last n times the upc was sold by date) from the SaleTable for each record in the InvTable.  The returned number of records needs to be for each upc, store, vendor.  So if I want to return the top 2 reocrds from the SaleTable it would be the lastest two dates that the upc was sold for each store and vendor.

Thanks,
John
0
Comment
Question by:JohnJMA
3 Comments
 
LVL 26

Assisted Solution

by:Shaun Kline
Shaun Kline earned 50 total points
ID: 35131340
Since you are using SS2K8, you can use Row_Number to limit your data:
http://msdn.microsoft.com/en-us/library/ms186734.aspx
0
 
LVL 40

Accepted Solution

by:
Sharath earned 450 total points
ID: 35131673
try this.
SELECT * 
  FROM (SELECT s.*, 
               ROW_NUMBER() 
                 OVER(PARTITION BY s.upc,s.vendor,s.store ORDER BY s.movement_date DESC) rn 
          FROM InvTable i 
               JOIN SalesTable s 
                 ON i.upc = s.upc 
                    AND i.vendor = s.vendor 
                    AND i.store = s.store) t1 
 WHERE rn <= 2

Open in new window

0
 

Author Closing Comment

by:JohnJMA
ID: 35141871
Thanks to both of you.  Sharath your solution was right on the mark.  Shaun Kline thanks for the link
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question