Solved

Select Last Sale Date

Posted on 2011-03-14
3
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 41

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

734 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