CTE not Evaluating All Selection Criteria Correctly [closed]

Closed. This question needs details or clarity. It is not currently accepting answers.

---

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 8 years ago.

Improve this question

Good Morning,

I've written the following stored procedure that searches for records that have one or more charge codes designated as a specified procedure and flags those as ProcGrouper=1 and need to identify those accounts that do not have contrast charges (flagged as ContrastGrouper=0).

When I run the sproc and audit accounts, I'm seeing many that have contrast charges. Thus, it appears that the CTE is not evaluating all criteria correctly. How might I remedy this?

with cteGrouper
as
(
SELECT [ENCOUNTER NUMBER],
[DATE OF SERVICE], [FEEDER KEY],
[CHGDTL CPT4 CODE], CASE
WHen [FEEDER KEY] IN ('76803520121','76803520120','76803520112','76803520111',
'76803520110','76803520109','76803520108','76803520034','76803520030','76803520027',
'76803520025','76803520017','76803520015','76803520014','76803510073','76803510072',
'76803510064','76803510012','76803510011','76803510008','76803510003','768035开发者_C百科10002',
'76803510068','76803520031','76803520028','76803520107','76803520022','76803520020',
'76803520106','76803510009','76803510070','76803510069','76803510067','76803510065',
'76803510074','76704027052','76606123003','76606123001','76606120012','76606111003',
'76606102043','76606100011','76606102040','76606121006','76606121009','76606123002',
'76606102053','76606102042') then 1 else 0
end as ProcGrouper, 
Case when [Feeder Key] in ('76806360076', '76806360077','76806360089','76806360090',
'76306360509','76306360513','76306360514','75706362105','76806366053','75706366054',
'75706366055','76606360099') then 1 else 0
end as ContrastGrouper
FROM ENCOUNTERCHGDTL
where ([Date of Service] between @StartDate and @EndDate)
),
cteFilterAccounts
as
(
Select [Encounter Number],
[Date of Service], [Feeder Key],
[Chgdtl CPT4 Code], ProcGrouper, ContrastGrouper
from cteGrouper
where ProcGrouper=1
and ContrastGrouper=1
)

Select [Encounter Number],
[Feeder Key],
[Date of Service],
[Chgdtl CPT4 Code]
,[ADMITDATE - CCYYMMDD], [DISCHARGEDATE - CCYYMMDD],
[DISCHARGEDISPOSITION], ProcGrouper, ContrastGrouper
from cteGrouper
join Encounter on [Encounter Number]=[EncounterNumber]
and AdmitSubService <>'SIG' 
and HSP#='1'
and ActualTotalCharge>0
where ProcGrouper=1 
and ContrastGrouper=0
and [Encounter Number] not in
(Select [Encounter Number] from cteFilterAccounts)

Order by [ENCOUNTER NUMBER]
END

---

I think you should redefine your cteFilterAccounts CTE. The thing is, your present code for that CTE returns the set where every individual row from cteGroup has both ProcGrouper=1 and ContrastGrouper=1. Based on the code for cteGroup, that can never be possible, because a row can have a [Feeder Key] belonging to no more than one of the two groups, so it's either ProcGrouper=1 or ContrastGrouper=1, but not both. Therefore, the CTE returns nothing.

Logically, though, you seem to be searching for rows that have both attributes set on the global scale, i.e. within the period. One possible solution could be to use a join, like this:

cteFilterAccounts AS (
  SELECT g1.[Encounter Number]
  FROM (
    SELECT DISTINCT [Encounter Number],
    FROM cteGrouper
    WHERE ProcGrouper=1
  ) g1
  INNER JOIN (
    SELECT DISTINCT [Encounter Number],
    FROM cteGrouper
    WHERE ContrastGrouper=1
  ) g2 ON g1.[Encounter Number] = g2.[Encounter Number]
)

This query joins all [Encounter Number] values with [Feeder Key] from one group that are found within the period, with a similar list where [Feeder Key] belongs to the other group. The matching values give you the resulting set.

Alternatively you could use INTERSECT:

cteFilterAccounts AS (
  SELECT [Encounter Number],
  FROM cteGrouper
  WHERE ProcGrouper=1
  INTERSECT
  SELECT [Encounter Number],
  FROM cteGrouper
  WHERE ContrastGrouper=1
)

Basically, it uses the same logic as join.

You could also try a completely different approach, grouping, like this:

cteFilterAccounts AS (
  SELECT [Encounter Number]
  FROM cteGrouper
  GROUP BY [Encounter Number]
  HAVING MAX(ProcGrouper) = 1
     AND MAX(ContrastGrouper) = 1
)

Here you are selecting distinct accounts that have both Grouper attributes' maximum (aggregated) values equal to 1.