Hello expert,
Payers have PAYER_IDS and PAYER_TYPE_CODES.
Looking for Payers that have two PAYER_TYPE_CODES
where those two PAYER_TYPES_CODES are 17001 and 17002.
Tried the query copied herewith below.
Returns all PAYER_IDS w PAYER_TYPE__CODES 17002
not just the PAYER_IDS w PAYER_TYPE__CODES 17002
and PAYER_TYPE__CODES 17001
Tried the query copied below with AND instead of OR
but this returned no rows.
Also tried WHERE IN (17001, 17002) Returns similar to OR.
I think I have done this using paretheses but can't remember.
Thanks.
Allen Pitts, Dallas Texas
With cte as (
select
PAyer_ID
FROM
CCM_PAYER_TYPE
group by PAyer_ID
having Count(*) =2
order by PAYER_ID)
SELECT PAYER_TYPE_ID,
CTE.PAYER_ID,
PAYER_TYPE_CODE
FROM CTE
join CCM_PAYER_TYPE PAT on PAT.payer_id =CTE.payer_id
where (PAYER_TYPE_CODE = 17001 or
PAYER_TYPE_CODE = 17002)
order by 2, 3
with CTE as
(
select payer_id from (
select payer_id,
count(case when payer_type_code = 17001 then 1 end) has_17001,
count(case when payer_type_code = 17002 then 1 end) has_17002,
count(*) total_count
from CCM_PAYER_TYPE
group by payer_id
)
where total_count=2 and has_17001=1 and has_17002=1
)
SELECT
CTE.PAYER_ID,
PAYER_TYPE_CODE
from CTE
join CCM_PAYER_TYPE PAT on CTE.PAYER_ID = PAT.PAYER_ID