CTEs in Stored Procedure Providing Unintended Results

Hi All,

I've written the following stored procedure which contains two CTEs.  The intent is to select those transactions that start with the letter J in the CPT field, flag the CPTGrouper on those records as 1, select transactions that have a set of feeder keys and set that flag as 1 (InfusionGrouper).  Ultimately, the stored procedure is intended to select those records with CPTGrouper=1 and InfusionGrouper =0.  However, when I review a sample of accounts in the result set, I am seeing account qualifying because they have a feeder key in the selected list.  Can anyone suggest a good way to remedy the following code to produce intended result?

Thanks,

Sid

ALTER PROCEDURE [dbo].[getInjectionAccts] 
	-- Add the parameters for the stored procedure here
	@StartDate varchar(8),
	@EndDate varchar(8)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	WITH cteGrouper
	AS
	(
	SELECT [ENCOUNTER NUMBER],  
	[FEEDER KEY], [CHGDTL CPT4 CODE],
	CASE WHEN [CHGDTL CPT4 CODE] LIKE 'J%' THEN 1 ELSE 0
	END AS InjGrouper,
	CASE WHEN [FEEDER KEY] IN ('62902600014','72312600012','70104500090',
	'62902600015','72312600013','70104500091','72312600008','74302600005',
	'62902600010','70104500094','70102600003','72312600009','74302600006',
	'62902600011','70104500095','70104500097','76423330705','70705100056',
	'74007600149','72313600201','62909404008','70104500061','70104500058',
	'76503330719') THEN 1 ELSE 0
	END AS InfusionGrouper
	
	from EncounterChgdtl
	WHERE [DATE OF SERVICE] BETWEEN @StartDate and @EndDate
	),
	
	cteFilterAccounts
	AS
	(
	SELECT [ENCOUNTER NUMBER],
		[FEEDER KEY],
		[CHGDTL CPT4 CODE],
		InjGrouper,
		InfusionGrouper
	FROM cteGrouper
	WHERE InjGrouper = 1 AND InfusionGrouper = 1
	)
	
	SELECT [ENCOUNTER NUMBER],  
[FEEDER KEY],
[CHGDTL CPT4 CODE],
InjGrouper,
InfusionGrouper,
[AdmitDate - CCYYMMDD],
[DischargeDate - CCYYMMDD],
DischargeDisposition  

FROM cteGrouper  
LEFT OUTER JOIN Encounter on [ENCOUNTER NUMBER]=[EncounterNumber]
WHERE InjGrouper =1 AND InfusionGrouper = 0
and [ENCOUNTER NUMBER] NOT IN 
(SELECT [ENCOUNTER NUMBER] FROM cteFilterAccounts)
and AdmitSubService <>'SIG' 
and HSP#='1'
and InOutCode='O'
and ActualTotalCharge>0
Order by [ENCOUNTER NUMBER]

END

Is it possible in your data to have a single encounter number that would have more than one return with different InjGrouper and InfusionGrouper results.

Like

EN  Inj  Inf

A     1      0

A     1      1

A     0      1

Daniel said:

Is it possible in your data to have a single encounter number that would have more than one return with different InjGrouper and InfusionGrouper results.

Like

EN  Inj  Inf

A     1      0

A     1      1

A     0      1

Hi Daniel,

The first and third scenarios could be true in that there could be transactions that are identified as an injection (CPT starts with J), and the same encounter has another transaction that meets the InfusionGrouper feeder key selection.  However, there are no feeder keys in the select statement that have a CPT code starting with J (your second scenario. 

I'm basically using the same pattern as you helped with earlier in the week for my ER accounts, as the logic is the same.  In this scenario, we want to identify those accounts that have an injection CPT code but do not have a feeder key identifying them as having an infusion.

Thanks,

Sid

I am still trying to get a handle on what you want.  Will this work:

SELECT [ENCOUNTER NUMBER],  
	[FEEDER KEY],
	[CHGDTL CPT4 CODE],
FROM EncounterChgdtl
WHERE [DATE OF SERVICE] BETWEEN @StartDate and @EndDate
	AND [CHGDTL CPT4 CODE] LIKE 'J%'
	AND [FEEDER KEY] NOT IN ('62902600014','72312600012','70104500090',
	'62902600015','72312600013','70104500091','72312600008','74302600005',
	'62902600010','70104500094','70102600003','72312600009','74302600006',
	'62902600011','70104500095','70104500097','76423330705','70705100056',
	'74007600149','72313600201','62909404008','70104500061','70104500058',
	'76503330719')
	AND AdmitSubService <>'SIG' 
	AND HSP#='1'
	AND InOutCode='O'
	AND ActualTotalCharge>0
Order by [ENCOUNTER NUMBER]

This worked!!  Thanks again:)