3d trolley

SQL view for last sale date of item


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.

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

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

    Like

    • 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

      Like

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

        Like

        • 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

          Like

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

            Like

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

    Dorothy

    Like

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

    Like

  4. Hi Victoria

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

    Thanks.

    Julian

    Like

    • 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

      Like

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

    Like

    • 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

      Like

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

    Like

    • 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

      Like

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

      Like

  7. Hi Victoria,
    Thank you o so much.

    Like

Trackbacks/Pingbacks

  1. SQL view for last sale date of item | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - March 31, 2011

    [...] Comments 0 Victoria expands on her previous SQL view and now gives us a SQL view for last sale date of item . [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,470 other followers

%d bloggers like this: