?
Solved

Top 1 Date per Customer

Posted on 2009-04-29
4
Medium Priority
?
249 Views
Last Modified: 2012-05-06
I have a table called EquipInvHist that contains sales transactions.  These records include the following fields:
CustomerSoldTo
SalesmanNo
CustomerInvoiceDate
In my query, I would like to return 1 row for each unique CustomerSoldTo value existing in the table, and show the most recent CustomerInvoiceDate, and the SalesmanNo from the entry with the most recent CustomerInvoiceDate.  I have written the select statement shown below, which is returning the most recent CustomerInvoiceDate for each distinct SalesmanNo who has sold to a CustomerSoldTo.  For example, if the table contains the following transactions for a customer:
CustomerSoldTo   SalesmanNo   CustomerInvoiceDate
100254                      44                1/23/09
100254                      55                2/15/08
100254                      55                3/1/08
my code below is currently returning the following rows for that customer:
CustomerSoldTo   SalesmanNo   CustomerInvoiceDate
100254                      44                1/23/09
100254                      55                3/1/08
I need it to only return the following row:
CustomerSoldTo   SalesmanNo   CustomerInvoiceDate
100254                      44                1/23/09

Any ideas as to how I can accomplish this?
select e.customersoldto, e.salesmanno, 
(select top 1 i.customerinvoicedate from equipinvhist i
where i.customersoldto = e.customersoldto
order by customerinvoicedate desc) as LastSaleDate
from equipinvhist e
group by e.customersoldto, e.salesmanno
order by e.customersoldto

Open in new window

0
Comment
Question by:customertrax
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1600 total points
ID: 24262956
select e.customersoldto, e.salesmanno,
from equipinvhist e
INNER JOIN
(select CustomerSoldTo, MIN(customerinvoicedate) customerinvoicedate  from equipinvhist
GROUP By CustomerSoldTo )  t
ON e.customersoldto = t.customersoldto and e.customerinvoicedate   = t.customerinvoicedate  
order by e.customersoldto
0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 400 total points
ID: 24263158
anees... I think you want max() not min().

customertax:  What DB Engine and version?  Also, what is your primary key?

Also try:


select e.customersoldto, e.salesmanno, e.customerinvoicedate
from equipinvhist e
where exists 
  (select customersoldto from equipinvhist where customersoldto=e.customersoldto having max(customerinvoicedate) = e.customerinvoicedate)

Open in new window

0
 
LVL 1

Author Comment

by:customertrax
ID: 24263680
That pointed me in the right direction...your code just needed a couple corrections.  I changed MIN to MAX, and added t.customerinvoicedate to the select statement.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 24264197
did mine not work at all?
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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