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 https://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.
Hi Victoria,
I’m hoping you can help me understand the results I’m seeing from this view. I see a number of items that returned multiple rows. They have different invoice numbers and different customers but the dates are the same. I’m assuming this is because the max function doesn’t look at the timestamp, just the date?
SELECT max(H.DOCDATE)
I am trying to join my view to this view so these duplicates are creating a bit of a problem.
Do you know of a way to exclude of the dupes?
select
a.[Item Number],
a.[Item Description],
a.[Location Code],
a.[QTY On Hand],
a.[QTY On Order],
a.[QTY Allocated],
a.[QTY Available],
a.[QTY Back Ordered],
b.Last_Sale,
a.[Current COST],
(a.[Current Cost] * a.[QTY On Hand]) as [Value]
from ItemQuantities a
LEFT JOIN view_Last_Sale_by_Item b on a.[Item Number] = b.Item_Number
Where
a.[Record Type] = ‘Overall’
and [QTY On Hand] <> 0
Thanks in advance. 🙂
LikeLike
Mark,
The code below will show you only one line per item, with the last sale date and highest (latest?) invoice number:
select
Item_Number, max(Invoice_Number) Last_Invoice, Last_Sale
from view_Last_Sale_by_Item
group by Item_Number, Last_Sale
Hope that helps,
-Victoria
LikeLike
Perfect. You rock Victoria! Thanks again.
LikeLike
Hi Victoria,
You helped me with the following code a few months ago which works great but I would like to add Item Class Code and User Field 1 from the Item Master table, IV00101. I’ve tried inner join which yields only the IV00101 columns as well as unions which errors do to the multiple select statements. I thought this would be a simple addition but it’s not turned out so for me. Can you assist ?
ORIGINAL CODE:
select distinct
MD.Last_Sale,
HH.CUSTNMBR Customer_ID,
HL.ITEMNMBR Item_Number,
HL.ITEMDESC Item_Description,
HL.UNITPRCE Unit_Price,
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
inner join
(select H.DOCDATE, D.ITEMNMBR, max(D.UNITPRCE) Max_Price
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 H.DOCDATE, D.ITEMNMBR) MP –max price
on HL.ITEMNMBR = MP.ITEMNMBR
and HL.UNITPRCE = MP.Max_Price
and MP.DOCDATE = MP.DOCDATE
where HH.SOPTYPE = 3 AND HH.VOIDSTTS = 0
MODIFIED CODE TO INCLUDE FIELDS FROM IV00101:
LikeLike
Hi Debi,
In the original code, before the very last line (the WHERE clause), add the following
left outer join IV00101 i
on HL.ITEMNMBR = i.ITEMNMBR
Then you can add whatever fields you want from the IV00101 table in the select list at the top.
-Victoria
LikeLike
Thanks so much! I thought it should be easy for someone who knows what they are doing but I am learning.
As always, I appreciate your willingness to help.
Regards,
Debi Welch, CPA â GP Consultant
SBS Group Corporate
Direct: 865.386.4862 | dwelch@sbsgroupusa.com
[sbs facebook] [sbs linkedin] [sbs twitter]
[cid:image004.jpg@01D2ADF2.445F1AA0]
LikeLike
Hi Victoria,
I realize this is an old post but I’ve used your view to create a report, modifying it slightly to include a few other fields. However, I would now like to add an additional filter to retrieve the highest ‘Unit Price’ if an item was sold on multiple invoices on the same last sale date. Is this an easy modification?
LikeLike
Hi Debi,
Please try this code:
select distinct
MD.Last_Sale,
HH.CUSTNMBR Customer_ID,
HL.ITEMNMBR Item_Number,
HL.ITEMDESC Item_Description,
HL.UNITPRCE Unit_Price,
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
inner join
(select H.DOCDATE, D.ITEMNMBR, max(D.UNITPRCE) Max_Price
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 H.DOCDATE, D.ITEMNMBR) MP --max price
on HL.ITEMNMBR = MP.ITEMNMBR
and HL.UNITPRCE = MP.Max_Price
and MP.DOCDATE = MP.DOCDATE
where HH.SOPTYPE = 3 AND HH.VOIDSTTS = 0
LikeLike
Victoria you are awesome! Thanks so much for the information and I’m sorry for the late response! Debi
LikeLike
This is great! How could I add to only show active items?
LikeLike
Shelly Ann,
How do you define an ‘active item’?
-Victoria
LikeLike
Hi Victoria! An item that is not discontinued and not marked inactive on the item maintenance card?
LikeLike
Shelly Ann,
Thanks, got it. You can add the following to the end of the WHERE clause (on line 40):
-Victoria
LikeLike
Shelly Ann,
Sorry, I always forget the default coding strips the less than and greater than signs out. It should be fixed now. Let me know if this works for you.
-Victoria
LikeLike
I’m having an issue with this one that I’m attempting to use in Excel. I’ve copied and pasted, but I’m getting a query error that says:
No column name was specified for cloumn 1 of ‘MD’.
Invalid column name ‘Last_Sale’.
Statement(s) could not be prepared.
I’ve tried changing some things in the SQL, but just don’t have enough smarts to get it to work. Any ideas?
LikeLike
Ryan,
I am not sure what you mean when you say you are “attempting to use in Excel” – can you please explain a little more? Are you able to create the view in SQL and have it return data?
-Victoria
LikeLike
Sorry… I cannot create the view in SQL. I enter your text into the SQL, which it gives me that error and will not return any data.
LikeLike
Ryan,
Hmmm…I just tested it and it worked for me. Are you making sure to get everything between the top and bottom ~~~~~~ ? Can you paste what you are copying into SQL into a comment here, maybe I can figure out what is going on from that.
-Victoria
LikeLike
Yes (see below). I believe it doesn’t recognizing “Last_Date” as the column name for “max(H.DOCDATE)” in the second INNER JOIN for some odd reason.
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 https://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
LikeLike
Ryan,
I think it’s having a problem with the — in front of “max date” a few lines from the bottom. Can you try removing everything after the “) MD” on that line?
-Victoria
LikeLike
Ryan,
I also just reformatted the code with a new option, maybe that will help. 🙂
-Victoria
LikeLike
I tried removing the “–max date”, but unfortunately it is still having the same problem.
LikeLike
Ryan,
I am going to email you to see if we can get this resolved.
-Victoria
LikeLike
Again you come through with exactly what I am looking for. YOU A ROCKSTAR!
Dorothy
LikeLike
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?
LikeLike
Hi Jim,
There are two places in the code where I have the following:
SOPTYPE = 3
To include quotes and orders you can replace these with:
SOPTYPE in (1,2,3)
-Victoria
LikeLike
Hi Victoria
How can I pick up the last 2 sales for any customer and item combination ?
Thanks.
Julian
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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.
LikeLike
Hi Victoria,
Thank you o so much.
LikeLike