dollar symbol

SQL view for Dynamics GP vendors with last activity date


I have seen many requests recently for a way to see what Dynamics GP vendors have had no recent activity. Since ‘recent’ can mean different things, I thought I would put together a view that shows the last activity date for each vendor. Once you have this you can easily filter for whatever date range you need. I also added the date the vendor was created, since a brand new vendor might not have any activity yet.

Here is a sample of the results (please click on the image to see it bigger):

Some additional resources:

create view view_Vendors_Last_Activity
as

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~
-- Created July 17, 2014 by Victoria Yudin
-- Flexible Solutions, Inc.
-- For other code, please visit http://victoriayudin.com
-- Returns all vendors with last activity date
-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

select
rtrim(v.VENDORID) Vendor_ID,
rtrim(v.VENDNAME) Vendor_Name,
max(coalesce(d.DOCDATE, '1/1/1900')) Last_Activity,
v.CREATDDT Created_Date

from PM00200 v

left outer join
(select VENDORID, DOCDATE from PM10000
   union
 select VENDORID, DOCDATE from PM20000
   union
 select VENDORID, DOCDATE from PM30200) d
    on v.VENDORID = d.VENDORID

group by v.VENDORID, v.VENDNAME, v.CREATDDT

go
grant select on view_Vendors_Last_Activity 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.

5 Responses to “SQL view for Dynamics GP vendors with last activity date”

  1. Thank you Victoria for sharing so many of these SQL gems!

    Leslie

    Like

Trackbacks/Pingbacks

  1. SQL view for Dynamics GP vendors with last activity date - Microsoft Dynamics GP Community - July 23, 2014

    […] out SQL view for Dynamics GP vendors with last activity date by Victoria Yudin Till […]

    Like

  2. SQL view for Dynamics GP vendors with last activity date – 7/17, Victoria Yudin | - July 17, 2014

    […] Continue reading on Source Blog […]

    Like

  3. DynamicAccounting.net | SQL view for Dynamics GP vendors with last activity date | Victoria YudinSQL view for Dynamics GP vendors with last activity date | Victoria Yudin - DynamicAccounting.net - July 17, 2014

    […] Yudin shows off a SQL view for Dynamics GP vendors with last activity date This is nice for cleaning out […]

    Like

  4. SQL view for Dynamics GP vendors with last activity date : Interesting Findings & Knowledge Sharing - July 17, 2014

    […] More: SQL view for Dynamics GP vendors with last activity date […]

    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: