web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Challenge with Script
Finance | Project Operations, Human Resources, ...
Suggested answer

Challenge with Script

(0) ShareShare
ReportReport
Posted on by Bill Campbell 12

select
format(max(docdate) , 'd','us') as DocDate ,
pk.vendorid ,
DOCNUMBR,
DOCTYPE
from pm00400 pk inner join pm00200 pm on pm.vendorid = pk.vendorid
group by pk.vendorid, DOCNUMBR , doctype
order by pk.vendorid

I want to pull the last (max docdate) document date from the PM00400 for a vendor and if it is after the > date, display this information.

I have removed all the other pieces from the PM00200 table that we are bringing over as it is just clutter.

CHALLENGE is that I can not get just the LAST Document Date.  When I run this I get all the transactions for the vendors.  It seems to ignor the MAX date 

I have tried a number of subquery selects, but I am doing something wrong.

Anyone got a fix for a tired consultant.

Categories:
Microsoft Dynamics GP
I have the same question (0)
  • Suggested answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at
    RE: Challenge with Script

    select DOCDATE,DOCTYPE, VENDORID,CNTRLNUM,DOCNUMBR from

    (select DOCDATE,DOCTYPE, PM.VENDORID,CNTRLNUM,DOCNUMBR,ROW_NUMBER() over (PARTITION BY  DOCTYPE, PM.VENDORID order by DOCDATE ASC)

    as MAXDATE from PM00400 PM inner join pm00200 VN on pm.vendorid = VN.vendorid

    )t where MAXDATE=1

  • Bill Campbell Profile Picture
    Bill Campbell 12 on at
    RE: Challenge with Script

    select pq.DOCDATE, pq.DOCTYPE, PQ.VENDORID,PQ.CNTRLNUM,PQ.DOCNUMBR ,

    (select PM.DOCDATE,PM.DOCTYPE, PM.VENDORID,PM.DOCNUMBR,ROW_NUMBER()

    over (PARTITION BY  DOCTYPE, PM.VENDORID order by DOCDATE ASC) as 'MaxDate Count'

    from PM00400 PM inner join pm00200 VN on pm.vendorid = VN.vendorid where docdate > '2017-09-30')

    FROM PM00400 pq inner join pm00200 pr on pr.vendorid = pq.vendorid

    This is the script I made (almost work), however I still get the error

    Msg 116, Level 16, State 1, Line 18

    Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

    When you have a chance please help me to fix this.  Again, objective is to get the LAST value from the PM00400 for a VENDOR as long as the date on the document is after the 2017-09-30

    Thanks.

  • Suggested answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at
    RE: Challenge with Script

    select DOCDATE,DOCTYPE, VENDORID,CNTRLNUM,DOCNUMBR from

(select DOCDATE,DOCTYPE, PM.VENDORID,CNTRLNUM,DOCNUMBR,ROW_NUMBER() over (PARTITION BY DOCTYPE, PM.VENDORID order by DOCDATE ASC)

as [MaxDate Count] from PM00400 PM inner join pm00200 VN on pm.vendorid = VN.vendorid where docdate > '2017-09-30'

)t
where [MaxDate Count]=1

    You don't need to use join again.

  • Richard Wheeler Profile Picture
    Richard Wheeler 75,848 Moderator on at
    RE: Challenge with Script

    Bill, be careful what you specify in the ROW OVER PARTITION clause. Almas is correct. I simply put her code through a SQL formatter to make it easier to read.

    SELECT docdate,
           doctype,
           vendorid,
           cntrlnum,
           docnumbr
    FROM   (SELECT docdate,
                   doctype,
                   PM.vendorid,
                   cntrlnum,
                   docnumbr,
                   Row_number()
                     OVER (
                       partition BY doctype, PM.vendorid
                       ORDER BY docdate ASC) AS MAXDATE
            FROM   pm00400 PM
            WHERE  vendorid IN (SELECT vendorid
                                FROM   pm00200)
                   AND docdate > '2017-09-30')t
    WHERE  maxdate = 1
    ORDER  BY vendorid 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 2,167

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 867 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 617 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans