March 31, 2011
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.
How do I modify this to also take into consideration sales orders and quotes. If it is not possible (or is too time consuming) to add all three into a singel script, how do I alter this to show the different types individually?
Hi Jim,
There are two places in the code where I have the following:
SOPTYPE = 3To include quotes and orders you can replace these with:
SOPTYPE in (1,2,3)-Victoria
Hi Victoria
How can I pick up the last 2 sales for any customer and item combination ?
Thanks.
Julian
Hi Julian,
This would involve much more complicated SQL code, most likely a stored procedure. It is not something that I anticipate posting on this blog in the foreseeable future, however, we can create this as a as consulting project if you are interested.
-Victoria
How do I show the First sale, Second Sale and third Sale of an item and who the customer was for that particular sale? all sales have different item descriptions and order dates.
Mustafa,
This would involve much more complicated SQL code, most likely a stored procedure. It is not something that I anticipate posting on this blog in the foreseeable future, however, we can create this as a as consulting project if you are interested.
-Victoria
Victoria,
I check this blog every once in a while as it is one of the few places on the net to find some good sql ideas for GP. But I must ask why is everything written in views? Is this for Smart List Builder? Or is it for Crystal? The above suggestion of putting 2 views together will work but will be very inefficient because of the nested inner join.
I am using a lot of SRS these days. I keep checking this site hoping to see SRS.
Thanks
b
—rewritten code using temp table
SELECT max(H.DOCDATE) Last_Sale, D.ITEMNMBR
into #MD 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
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
#MD MD– max date
ON HL.ITEMNMBR = MD.ITEMNMBR
AND HH.DOCDATE = MD.Last_Sale
WHERE
HH.SOPTYPE = 3
AND HH.VOIDSTTS = 0
B,
This blog was created to help people find tables and additional information for reporting on Dynamics GP data and give some ideas and examples for reporting. I try to keep the code fairly easy and straightforward so someone can see how to join tables and get a start on their report. I use views because they are so universal – they can be used ‘as is’ for SmartList Builder, Crystal, Excel and SRS, which are the top 4 requests that we get (probably in that order). Anyone with experience coding in SQL can easily enough take what I post and use it as a start to create their own stored procedure, or add/change whatever they need. Anyone not too familiar with coding in SQL can use what I post without any modifications and it will just work for them.
What specifically are you looking for in terms of SRS? For the last year we have been putting SRS tips into our monthly GP Reports Viewer newsletters (there is a sign up link on the right side of this blog).
-Victoria
Actually, b, your example is at best on par with Victoria’s example, and in all likelihood performs even worse. It seems like the system is doing the same job twice in her example, but the query engine is actually smart enough to parse backwards through the join and use indexes and statistics to return the data.
Creating a temp table and following the two-step process that you’ve outlined is really what SQL server would do internally itself, assuming that it had no indexes or statistics to work from and had to do full-table-scans accross the board.
If Victoria were to add an index to the Document Date column on the SOP30200 table, her view would be about as efficient as you can get for this type of query. Even better, being in a view means she can join it to more complex queries, and the join conditions would flow back through the view, restricting the data appropriately so that the system only needs to pull the data required, again, using indexes instead of full table scans.
The nice thing about SQL queries is that the simpler you make them, the more efficient they are even for the SQL query engine to execute them.
Hi Victoria,
Thank you o so much.