SQL view for last sale date of item

An interesting question came up in response to my last SQL view showing the last sale by customer and item. How do you show the latest sale of an item and who the customer was for that particular sale?

This is a bit more difficult, as you have to make two passes through the data, first to find the latest date and then to pull out the information for that date only. There are also many things to consider when writing a report like this – for example how do you handle multiple sales of the same item on the same date? Do you show all the customers or just one?  If just one, which one? Also, what happens if you have a different item description? Is that considered a different item?  

The view below shows the last sale date of an item (using Invoice Date) and will return multiple lines if the same item was sold to more than one customer on that date. (So item ABC was last sold on 3/31/2011, and 3 different customers bought it on that date, you will see 3 individual lines in the results, one for each customer.) It will also show different item descriptions as different items, but only for the last sale date of the item. (So if item XYZ was last sold on 3/31/2011, but there are two different item descriptions in SOP10200 for the items sold on that date, they will show up as different lines in the results.)

~~~~~

CREATE VIEW view_Last_Sale_by_Item
AS

/*******************************************************************
view_Last_Sale_By_Item
Created on Mar 31, 2011 by Victoria Yudin - Flexible Solutions, Inc.
For updates see http://victoriayudin.com/gp-reports/
Only looks at posted SOP invoices that are not voided.
Multiple customer sales on the same date are shown as separate lines.
Different item descriptions for items sold on the same date are shown
	as separate lines. 

Tables used:
HH - History Header - SOP30200
HL - History Line - SOP30300
*******************************************************************/

SELECT DISTINCT
	MD.Last_Sale,
	HH.CUSTNMBR Customer_ID,
	HL.ITEMNMBR Item_Number,
	HL.ITEMDESC Item_Description,
	HH.SOPNUMBE Invoice_Number

FROM
	SOP30200 HH

INNER JOIN
	SOP30300 HL
	ON HH.SOPTYPE = HL.SOPTYPE
	AND HH.SOPNUMBE = HL.SOPNUMBE

INNER JOIN
	(SELECT max(H.DOCDATE) Last_Sale, D.ITEMNMBR
	 FROM SOP30200 H
	 INNER JOIN SOP30300 D
		ON H.SOPTYPE = D.SOPTYPE
		AND H.SOPNUMBE = D.SOPNUMBE
	 WHERE H.SOPTYPE = 3 AND H.VOIDSTTS = 0
	 GROUP BY D.ITEMNMBR) MD -- max date
	ON HL.ITEMNMBR = MD.ITEMNMBR
	AND HH.DOCDATE = MD.Last_Sale

WHERE
	HH.SOPTYPE = 3
	AND HH.VOIDSTTS = 0

/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Last_Sale_By_Item TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.