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 -- 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 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.