empty cart

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.

15 Responses to “SQL view for last sale by customer and item”

  1. Hi Victoria, should we not include SOP10200 as well to make sure that the customer is not having an open order.

    Like

    • Zafar,

      Good question. The answer would depend on how Kirk’s company uses GP. Many companies only enter invoices into GP without using Quotes or Orders, in which case just SOP30200 would be fine. Also, some companies may need to look at the RM tables, as those might have additional invoices not captured in SOP tables.

      -Victoria

      Like

      • Zafar,
        Thank you for your suggestion. Open orders don’t typically linger in our system long enough to have any real effect on this particular project, however I will keep this in mind in the future.

        Victoria,
        Thank you for your help. I was able to run that script against my test database and it worked perfectly. Now its back to my sql book so I can make my queries look more like yours :)

        Cheers,
        Kirk

        Like

  2. Hi Victoria,
    Thank you for maintaining this blog. It has saved me countly hours of research.

    I am working on writing a query (to eventually make a view) that returns the last invoice date for customers that is at least 190 days old. I am new to sql queries and am guessing that I am missing something very simple. Any help you could provide would be appreciated.

    select RM00101.[CUSTNMBR]as ‘Customer Number’, RM00101.[CUSTNAME]
    as ‘Customer Name’, RM00101.[STATE],
    RM00101.[PHONE1] as ‘Phone 1′, RM00101.[CNTCPRSN]
    as ‘Contact Person’, RM00101.[CUSTCLAS] as ‘Customer Class’,
    RM00101.[SLPRSNID] as ‘Saleperson ID’, RM00103.[TTLSLLTD]
    as ‘Life to Date’, RM00103.[TTLSLYTD] as ‘Year to Date’, RM00103.[TTLSLLYR]
    as ‘Last Years Sales’, MAX(SOP30200.[DOCDATE]) as ‘Last Invoice Date’
    from RM00101 left outer join SOP30200
    on RM00101.[CUSTNMBR] = SOP30200.[CUSTNMBR] inner join RM00103
    on RM00101.[CUSTNMBR] = RM00103.[CUSTNMBR]
    and SOP30200.[VOIDSTTS] = 0 and RM00101.INACTIVE = 0 and RM00101.[HOLD] = 0 and SOP30200.[SOPTYPE] = 3 and datediff(d,SOP30200.[DOCDATE], getdate()) < '190' group by RM00101.[CUSTNMBR],
    RM00101.[CUSTNAME], RM00101.[STATE],
    RM00101.[PHONE1], RM00101.[CNTCPRSN], RM00101.[CUSTCLAS],
    RM00101.[SLPRSNID], RM00103.[TTLSLLTD], RM00103.[TTLSLYTD], RM00103.[TTLSLLYR]

    Thanks again,
    Kirk

    Like

    • Hi Kirk,

      Your query looks good – however, it is returning the last SOP invoice in the last 190 days only, not invoices that are ‘at least 190 days old’. If you only wanted to consider invoices that are 190 days or older, then you can change the = (greater than or equals) in front of the 190. Also, you do not need the quotes around the 190, since that is a number. It seems to work with or without them in SQL 2012, where I tested this, but just wanted to mention it.

      If I am not understanding your question or if you need additional help, let me know.

      -Victoria

      Like

      • Victoria,
        Thank you for your response. I think I may have been unclear with what I am trying to accomplish with this script.

        My end goal is to have a list of customers whose most recent invoice is at least 190 days old. Eventually I would like to make this into a view and then add it to a smartlist that can be checked by our customer service reps to make a follow up call to those cusotmers that haven’t purchased recently.

        Kirk

        Like

        • Kirk,

          Ahhh…yes, that’s a bit different. Try the following – the ‘having’ clause at the end is the key to the logic. I also ‘simplified’ your query a little to take out unneeded syntax. I find it easier to read this way, plus you don’t have to type as much. :-)

          select 
          c.CUSTNMBR 'Customer Number', 
          c.CUSTNAME 'Customer Name', 
          c.[STATE] 'State',
          c.PHONE1 'Phone 1', 
          c.CNTCPRSN 'Contact Person', 
          c.CUSTCLAS 'Customer Class',
          c.SLPRSNID 'Saleperson ID', 
          cs.TTLSLLTD 'Life to Date', 
          cs.TTLSLYTD 'Year to Date', 
          cs.TTLSLLYR 'Last Years Sales',
          max(s.DOCDATE) 'Last Invoice Date'
          from RM00101 c  --customers
          left outer join SOP30200 s  --SOP
          on c.CUSTNMBR = s.CUSTNMBR 
          inner join RM00103 cs  --customer summary
          on c.CUSTNMBR = cs.CUSTNMBR
          where 
          s.VOIDSTTS = 0 and c.INACTIVE = 0 and c.HOLD = 0 and s.SOPTYPE = 3 
          group by c.CUSTNMBR, c.CUSTNAME, c.[STATE], c.PHONE1, c.CNTCPRSN, 
          c.CUSTCLAS, c.SLPRSNID, cs.TTLSLLTD, cs.TTLSLYTD, cs.TTLSLLYR
          having datediff(d,max(s.DOCDATE), getdate()) >= 190 
          

          -Victoria

          Like

  3. Hi Victoria,
    Thank you so much.

    Like

  4. Hi Victoria,
    How can we modify this view to get the last sale for each items and the corresponding customer instead of last sale for each item for each customer. In General, when we are using Max for Date, which grouping we can use to get corresponding record for any other field. Usually I use two views to get these types of results but would like to know if we can get the same result by only one simple view.

    Like

    • Hi Zafar,

      Two views is not a bad idea and there is certainly nothing wrong with that approach. To do it in one view you would need to do a join to the same set of data inside the view, linking on the max you have found. I will see if I have some time to out together an example over the next few days.

      -Victoria

      Like

Trackbacks/Pingbacks

  1. GP 2010 Item last sold: Including BOM components | dorothyjarry - April 23, 2013

    [...] with the SQL view for last sale by customer and item by Victoria Yudin with a couple modifications.  I created some queries to recursively capture all [...]

    Like

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

    [...] 0 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 [...]

    Like

  3. SQL view for last sale date of item | Victoria Yudin - March 31, 2011

    [...] 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 [...]

    Like

  4. SQL view for last sale by customer and item | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - March 30, 2011

    [...] 0 Victoria Yudin delivers a new SQL view for last sale by customer and item. This got me thinking. With this as a base, I could figure out an average time between orders and [...]

    Like

  5. Last Sale By a Customer & Item - Victoria's SQL View - Microsoft Dynamics GP Learn & Discuss - GP Technical Blogs - Microsoft Dynamics Community - March 30, 2011

    [...] 0 Victoria's latest SQL view retrieves the last sale by customer and an item. With little tweaks here and there, this SQL view will serve us immensely to show various [...]

    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,532 other followers

%d bloggers like this: