SQL view for last sale by customer and item

When was the last time a particular customer bought a particular item? Here is a little view that will tell you.

For other Dynamics GP views and reporting tips, take a look at my GP Reports page. Or check out the SOP Tables page for more details about reporting on SOP data.

~~~~~

CREATE VIEW view_Last_Sale_By_Customer_Item
AS

/*******************************************************************
view_Last_Sale_By_Customer_Item
Created on Mar 29, 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

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

SELECT
	max(HH.DOCDATE) Last_Sale,
	HH.CUSTNMBR Customer_ID,
	HL.ITEMNMBR Item_Number,
	HL.ITEMDESC Item_Description

FROM
	SOP30200 HH

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

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

GROUP BY
	HH.CUSTNMBR,
	HL.ITEMNMBR,
	HL.ITEMDESC

/** 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_Customer_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.