Solved

Select Last Sale Date

Posted on 2011-03-14
3
432 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now