SQL view for last sale date of item

March 31, 2011 by Victoria Yudin 35 Comments

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.

Dynamics GP, GP Reports code, GP SQL scripts, SOP SQL code

GP Reports code, GP SQL view, Sales Order Processing, SQL code

SQL view for last sale by customer and item

SQL view for all GL transactions in Dynamics GP

35 Responses to “SQL view for last sale date of item”

Mark Reply April 18, 2018 at 2:56 pm

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
- Victoria Yudin Reply April 18, 2018 at 3:06 pm
  
  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
  - Mark Reply April 18, 2018 at 7:10 pm
    
    Perfect. You rock Victoria! Thanks again.
    
    LikeLike
Debi Welch Reply May 24, 2017 at 10:22 am
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:
```
(select distinct
HL.ITEMNMBR Item_Number,
HL.ITEMDESC Item_Description,
HL.CONTITEMNBR AS Bin,
HL.QUANTITY AS Matl_Qty,
HL.UNITPRCE Unit_Price,
MD.Last_Sale,
HL.UNITCOST AS Unit_Cost_FOB,
I.ITMCLSCD,
I.USCATVLS_1 AS Source
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
inner join
(select H.DOCDATE, D.ITEMNMBR, D.UNITPRCE, max(H.SOPNUMBE) Invoice_Number
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, D.UNITPRCE) MS --max invoice number
on HL.ITEMNMBR = MS.ITEMNMBR and HL.UNITPRCE = MP.Max_Price AND HH.SOPNUMBE = MS.Invoice_Number and MS.Invoice_Number = MS.Invoice_Number
where HH.SOPTYPE = 3 AND HH.VOIDSTTS = 0)
UNION ALL
SELECT
HL.ITEMNMBR Item_Number,
HL.ITEMDESC Item_Description,
HL.CONTITEMNBR AS Bin,
HL.QUANTITY AS Matl_Qty,
HL.UNITPRCE Unit_Price,
MD.Last_Sale,
HL.UNITCOST AS Unit_Cost_FOB,
I.ITMCLSCD AS Class,
I.USCATVLS_1 AS Source
from SOP30300 HL, IV00101 I,SOP30200 H, SOP30300 D, SOP30200 HH
```
LikeLike
- Victoria Yudin Reply May 24, 2017 at 10:57 am
  
  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
  - Debi Welch Reply May 24, 2017 at 11:38 am
    
    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
Debi Welch Reply January 11, 2017 at 1:52 pm

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
- Victoria Yudin Reply January 16, 2017 at 8:20 am
  
  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
  - Debi Reply February 3, 2017 at 8:37 am
    
    Victoria you are awesome! Thanks so much for the information and I’m sorry for the late response! Debi
    
    LikeLike
Shely lAnn Reply December 22, 2014 at 5:10 pm

This is great! How could I add to only show active items?

LikeLike
- Victoria Yudin Reply December 22, 2014 at 5:24 pm
  
  Shelly Ann,
  
  How do you define an ‘active item’?
  
  -Victoria
  
  LikeLike
  - Shelly Ann Reply December 22, 2014 at 5:27 pm
    
    Hi Victoria! An item that is not discontinued and not marked inactive on the item maintenance card?
    
    LikeLike
    - Victoria Yudin Reply December 22, 2014 at 5:35 pm
      
      Shelly Ann,
      
      Thanks, got it. You can add the following to the end of the WHERE clause (on line 40):
      
      and HL.ITEMNMBR in (select ITEMNMBR from IV00101 where ITEMTYPE <> 2 and INACTIVE = 0)
      
      -Victoria
      
      LikeLike
      - Victoria Yudin Reply December 22, 2014 at 5:37 pm
        
        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
ryan Reply September 13, 2013 at 2:35 pm

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
- Victoria Yudin Reply September 13, 2013 at 3:23 pm
  
  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
  - ryanhise Reply September 13, 2013 at 3:44 pm
    
    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
    - Victoria Yudin Reply September 13, 2013 at 3:48 pm
      
      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
      - ryanhise Reply September 13, 2013 at 3:58 pm
        
        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
        
        Victoria Yudin Reply September 13, 2013 at 4:02 pm
        
        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
        
        Victoria Yudin Reply September 13, 2013 at 4:07 pm
        
        Ryan,
        
        I also just reformatted the code with a new option, maybe that will help. 🙂
        
        -Victoria
        
        LikeLike
        
        ryanhise Reply September 13, 2013 at 4:09 pm
        
        I tried removing the “–max date”, but unfortunately it is still having the same problem.
        
        LikeLike
        
        Victoria Yudin Reply September 13, 2013 at 4:15 pm
        
        Ryan,
        
        I am going to email you to see if we can get this resolved.
        
        -Victoria
        
        LikeLike
dorothyjarry Reply April 9, 2013 at 1:46 pm

Again you come through with exactly what I am looking for. YOU A ROCKSTAR!

Dorothy

LikeLike
Jim Taft Reply May 27, 2011 at 8:01 am

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
- Victoria Yudin Reply May 27, 2011 at 8:20 am
  
  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
Julian Reply April 19, 2011 at 6:12 am

Hi Victoria

How can I pick up the last 2 sales for any customer and item combination ?

Thanks.

Julian

LikeLike
- Victoria Yudin Reply April 21, 2011 at 12:54 pm
  
  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
Mustafa Abdussalam Reply April 4, 2011 at 3:55 am

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
- Victoria Yudin Reply April 21, 2011 at 12:54 pm
  
  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
b Reply April 4, 2011 at 12:48 am

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
- Victoria Yudin Reply April 4, 2011 at 5:05 am
  
  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
- Ken Webber Reply October 27, 2011 at 8:14 pm
  
  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
Zafar Khan Reply March 31, 2011 at 1:57 pm

Hi Victoria,
Thank you o so much.

LikeLike