[Lydia Ebinyo Derri]

Hello,

I am trying to query vendors that has been one-time payment vendors.

Would anyone provide a sql query that will pull in vendors that has one doctype of either 1,2 or 3 AND a payment using doctype of either 4,5 or 6?

——————————
Lydia Ebinyo Derri
OSU Physicians, Inc
Columbus OH
——————————

[Victoria Yudin]

Hi Lydia,

Try the following:

select VENDORID from PM00200
where VENDORID in
(--vendors with only one debit
select VENDORID
from
(select VENDORID from PM20000 where DOCTYPE in (1,2,3)
group by VENDORID having count(*) = 1
union all
select VENDORID from PM30200 where DOCTYPE in (1,2,3)
group by VENDORID having count(*) = 1) a
group by a.VENDORID having count(*) = 1
)
and VENDORID in
(--vendors with only one credit
select VENDORID
from
(select VENDORID from PM20000 where DOCTYPE in (4,5,6)
group by VENDORID having count(*) = 1
union all
select VENDORID from PM30200 where DOCTYPE in (4,5,6)
group by VENDORID having count(*) = 1) a
group by a.VENDORID
having count(*) = 1
)

——————————
Victoria Yudin
Microsoft Dynamics GP MVP 2005-2016
Flexible Solutions, Inc.
http://www.GPReportsViewer.com
http://victoriayudin.com
——————————
——————————————-

[Adam Jacobson]

Victoria’s solution will certainly work.
This should also work
WITH TransRecs (Vendor, DocType)
AS
(
SELECT VENDORID, DOCTYPE FROM PO20000
UNION ALL
SELECT VENDORID, DOCTYPE FROM PO30200
)
SELECT
Vendor,
COUNT(CASE WHEN DocType IN (1,2,3) THEN 1 ELSE 0 END) AS Type123,
COUNT(CASE WHEN DocType IN (4,5,6) THEN 1 ELSE 0 END) AS Type456
FROM TransRecs
GROUP BY Vendor
HAVING COUNT(CASE WHEN DocType IN (1,2,3) THEN 1 ELSE 0 END) = 1
AND COUNT(CASE WHEN DocType IN (4,5,6) THEN 1 ELSE 0 END) = 1

——————————
Adam Jacobson
President
Red Three Consulting, Inc.
Bronx NY
——————————
——————————————-

[Adam Jacobson]

Typo fixed

WITH TransRecs (Vendor, DocType)
AS
(
SELECT VENDORID, DOCTYPE FROM PM20000
UNION ALL
SELECT VENDORID, DOCTYPE FROM PM30200
)
SELECT
Vendor,
COUNT(CASE WHEN DocType IN (1,2,3) THEN 1 ELSE 0 END) AS Type123,
COUNT(CASE WHEN DocType IN (4,5,6) THEN 1 ELSE 0 END) AS Type456
FROM TransRecs
GROUP BY Vendor
HAVING COUNT(CASE WHEN DocType IN (1,2,3) THEN 1 ELSE 0 END) = 1
AND COUNT(CASE WHEN DocType IN (4,5,6) THEN 1 ELSE 0 END) = 1

——————————
Adam Jacobson
President
Red Three Consulting, Inc.
Bronx NY
——————————
——————————————-

[John Arnold]

Adam,

I have not played with your query, but after a visual inspection I think there may be a problem with the four COUNTs in the query:

COUNT(CASE WHEN DocType IN (1,2,3) THEN 1 ELSE 0 END) AS Type12

The COUNT function is going to count each row even if the DocType is not the (1,2,3) or (4,5,6) groups because they aren’t returning a NULL.  Should you use SUM instead of COUNT?  If you want to use the COUNT, should you return NULL in the ELSE clause instead of 0?  I hope this helps!

Thanks

——————————
John Arnold
US Digital
Vancouver WA
——————————
——————————————-