Skip to Content
1

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

Mar 14, 2017 at 02:40 PM

66

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Johan Hakkesteegt Mar 15, 2017 at 08:13 AM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hi,

Very Well Thank You

You have created Query .this Query most helpful for me.

Now I have created Query.

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

select [Supplier Number],[Supplier Name ]

,convert(nvarchar(10),max([Date last used]),103) AS 'Date last used'

from (SELECT c.CardCode as 'Supplier Number' ,c.CardName as 'Supplier Name ' ,

(select top 1 DocDate from OPOR where CardCode = c.CardCode order by DocDate desc) AS 'Date last used'

FROM OCRD c WHERE c.CardType = 'S'

union all

SELECT c.CardCode as 'Supplier Number' ,

c.CardName as 'Supplier Name ' ,

(select top 1 DocDate from OPDN where CardCode = c.CardCode order by DocDate desc) AS 'Date last used'

FROM OCRD c WHERE c.CardType = 'S'

union all

SELECT c.CardCode as 'Supplier Number' ,c.CardName as 'Supplier Name ' ,

(select top 1 DocDate from ORPD where CardCode = c.CardCode order by DocDate desc) AS 'Date last used'

FROM OCRD c WHERE c.CardType = 'S'

union all

SELECT c.CardCode as 'Supplier Number' ,c.CardName as 'Supplier Name ' ,

(select top 1 DocDate from OPCH where CardCode = c.CardCode order by DocDate desc) AS 'Date last used'

FROM OCRD c WHERE c.CardType = 'S'

union all SELECT c.CardCode as 'Supplier Number' ,c.CardName as 'Supplier Name ' ,

(select top 1 DocDate from ORPC where CardCode = c.CardCode order by DocDate desc) AS 'Date last used'

FROM OCRD c WHERE c.CardType = 'S'

union all

SELECT c.CardCode as 'Supplier Number' ,c.CardName as 'Supplier Name ' ,

(select top 1 DocDate from OVPM where CardCode = c.CardCode order by DocDate desc) AS 'Date last used'

FROM OCRD c WHERE c.CardType = 'S' )

Result group by result.[Supplier Number],

result.[Supplier Name ]

order by result.[Supplier Number]

0

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

1
Johan Hakkesteegt Mar 14, 2017 at 02:58 PM
0

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

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thank you so much for the reply.

Last used specifically means the date last used of that particular supplier in any of the document.

Kindly help if possible

0

Hello

I have 5000 suppliers but some of them are unused from the last 1 or 2 years. ( for inactive process)

Can you provide a Query to select only those which are recently used.

0