Skip to Content

all suppliers (supplier number, supplier name) and if possible date last used

Select T0.[CardCode] as 'Supplier Number' ,

T0.[CardName] as 'Supplier Name '

,(select top 1 A.updatedate

from adoc A where a.[cardcode] = t0.[cardcode]

order by a.UpdateDate desc) 'Date last used'

FROM ocrd t0 where t0.[cardtype] = 's'

Q. lists all suppliers (supplier number, supplier name) and if possible date last used ?

1}In the above query when executed, the shown purchase orders sometimes have dates but sometimes they don't.

I want to know the reason for that because for the same entry in the history, the date is shown.

Can someone help

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Mar 15, 2017 at 08:13 AM

    Hi,

    How about this:

    SELECT c.CardCode
          ,c.CardName
          ,(select top 1 DocDate from OPOR where CardCode = c.CardCode order by DocDate desc) AS MostRecent_PO
          ,(select top 1 DocDate from OPDN where CardCode = c.CardCode order by DocDate desc) AS MostRecent_GRPO
          ,(select top 1 DocDate from OPCH where CardCode = c.CardCode order by DocDate desc) AS MostRecent_AP_Invoice
    FROM OCRD c
    WHERE c.CardType = 'S'
      AND (c.CardCode IN (select distinct CardCode
                          from OPOR
                          where DocDate >= DATEADD(YEAR, -2, GETDATE()))
        OR c.CardCode IN (select distinct CardCode
                          from OPDN
                          where DocDate >= DATEADD(YEAR, -2, GETDATE()))
        OR c.CardCode IN (select distinct CardCode
                          from OPCH
                          where DocDate >= DATEADD(YEAR, -2, GETDATE())))

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Hi,

      Looks good, but this returns more results than my query did. Please check that your query now gives you the result set that you expected.

      Please close this thread by selecting best answer. You may convert your comment to an answer, so you can select it as best answer if you want.

      Regards,

      Johan

  • Mar 14, 2017 at 02:58 PM

    Hi,

    The history log will show all changes made to a document, but if no changes were ever made there is no entry in ADOC. In that case, the history log will simply show one entry, which refers to the document itself.

    Your query now only shows a date when changes have been made to any document for that supplier (PO. GRPO, incoming invoice, etc.). In other words it will not really tell when that supplier has last been used.

    You may want to determine more specifically what "last used" actually means to you. Then we can can come up with a better query.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded