March 29, 2011 
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 https://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.
Victoria Yudin 
Hi Victoria! I am trying to use this to get the last price paid and the cost of each item. When I add those fields from the sop30300 I get extra records if the customer paid a different price for the same item. How can I get it to show just the last price paid?
LikeLike
Hi Vic,
You won’t be able to simply add the unit price to this code, that would result is exactly what you’re seeing – duplicated data. You can try something like this instead:
select d.* from(select h.SOPNUMBE, CUSTNMBR,
DOCDATE, ITEMNMBR, UNITPRCE
from SOP30200 h
inner join SOP30300 d
on h.SOPNUMBE = d.SOPNUMBE
and d.SOPTYPE = 3
where h.SOPTYPE = 3) d
inner join
(select CUSTNMBR,
max(DOCDATE) latest, ITEMNMBR
from SOP30200 h
inner join SOP30300 d
on h.SOPNUMBE = d.SOPNUMBE
and d.SOPTYPE = 3
where h.SOPTYPE = 3
group by CUSTNMBR, ITEMNMBR) m
on d.DOCDATE = m.latest
and d.CUSTNMBR = m.CUSTNMBR
and d.ITEMNMBR = m.ITEMNMBR
-Victoria
LikeLike
As usual Victoria to the RESCUE. You should have a cape!!! THANK YOU
LikeLike
Hi Victoria, should we not include SOP10200 as well to make sure that the customer is not having an open order.
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
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. 🙂
-Victoria
LikeLike
Hi Victoria,
Thank you so much.
LikeLike
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.
LikeLike
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
LikeLike