SQL 2005 - Query Help / Suggestion

Please note, the SQL is handled dynamically by the SQL server, therefore some of whats in my WHERE clauses will look odd to you.

My question has to do with this one specific section.

--- delete out the post op visits - CPTCode 99024
delete t from #temp t
join PatientVisitProcs pvp on t.patientvisitid=pvp.patientvisitid
where pvp.CPTCode IN ('99024')

My client does not want to return the visits where only the '99024' (pvp.CPTCode) was billed. This morning, I created two dummy visits - one with just the 99024 CPT Code and another with the 99024 and another code. Based on my current coding below, it wiped off both visits. I need it only to wipe off the visits where 99024 is the only code billed. If the 99024 is billed with any other code, return that visit.

SET NOCOUNT ON
 
declare 	
	@groupby1 varchar(60),
	@groupby2 varchar(60)
 
set @groupby1 = convert(varchar(60),'None')
set @groupby2 = convert(varchar(60),'None')
 
SELECT DISTINCT
		a.ApptStart,
    	a.appointmentsID, 
    	pp.first as ApptPatFirst,
    	pp.last as ApptPatLast,
    	adr.listname as ApptResource,
    	afac.listname as ApptFacility,
    	apt.name as ApptType,
    	ppas.name as ApptAllocationSet,
    	pv.PatientVisitId,
    	pv.Ticketnumber,
    	pv.entered as VisitEntered,    
    	pm.dateofentry as PaymentDateofEntry,
    	case pm.payerType
		when 'Guarantor' then g2.last 
		when 'Patient' then pp.last
		else NULL
    	end as PayerLast,
    	case pm.payerType
		when 'Guarantor' then g2.first 
		when 'Patient' then pp.first
		else NULL
    	end as PayerFirst,
    	td.amount as PaymentAmount,
    	pt.description as PaymentType,
	CASE	
		WHEN pm.PaymentType = 1 THEN 'Cash'
		WHEN pm.PaymentType = 2 THEN 'Check' 
		WHEN pm.PaymentType = 3 THEN 'Credit Card' 
		WHEN pm.PaymentType = 4 THEN 'EFT'
		WHEN pm.PaymentType = 5 THEN 'Money Order'
		WHEN pm.PaymentType = 6 THEN 'Conveyance'
		ELSE 'N/A'
	END AS PaymentMethod,
	case @groupby1
		when 'Resource' then adr.listname
		when 'Facility' then afac.listname 
		else 'None'
    	end as Groupby1,
	case @groupby2
		when 'Resource' then adr.listname
		when 'Facility' then afac.listname 
		else 'None'
    	end as Groupby2
into #temp            
 
FROM 	
	appointments a
    	left join appttype apt on a.appttypeID =apt.appttypeID
    	left join doctorfacility adr on a.resourceID = adr.doctorfacilityID
    	left join doctorfacility afac on a.facilityID = afac.doctorfacilityID
    	left join patientprofile pp on a.ownerID = pp.patientprofileID
    	left join allocationset ppas on pp.allocationsetID = ppas.allocationsetID
    	left join patientvisit pv on a.patientvisitID = pv.patientvisitID
--    	left join patientvisitprocs pvp on pv.patientvisitID = pvp.patientvisitID and pvp.CPTCode NOT IN ('99024')
    	left join visittransactions vt on pv.patientvisitID = vt.patientvisitID
    	left join transactions t on vt.visittransactionsID = t.visittransactionsID
    	left join transactiondistributions td on t.transactionsID = td.transactionsID
    	left join (select * from medlists where tablename = 'paymenttypes') pt on t.actiontypeMID = pt.medlistsID
    	left join paymentmethod pm on vt.paymentmethodID = pm.paymentmethodID and pm.source =1
    	left join guarantor g2 on pm.payerID = g2.guarantorID
 
WHERE 
    	a.apptkind =1
    	and isnull(a.canceled,0) = 0
    	AND a.ApptStart>=ISNULL('04/21/2009','1/1/1900')
    	And a.ApptStart<dateadd(day,1,ISNULL('04/21/2009','1/1/3000'))
        	AND  --Filter on resource
	(
	(NULL IS NOT NULL AND a.resourceID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND a.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
    	AND --Filter on Appt Type
	(
	(NULL IS NOT NULL AND a.ApptTypeID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on allocation set
	(
	(NULL IS NOT NULL AND pp.AllocationSetID IN (NULL)) OR
	(NULL IS NULL)
	)
 
--- delete out the post op visits - CPTCode 99024
delete t from #temp t
join PatientVisitProcs pvp on t.patientvisitid=pvp.patientvisitid
where pvp.CPTCode IN ('99024')
	
 
--clean out ins pmts the inadvertently get picked up.....
update #temp
set paymentamount = null,
	paymenttype = null
where paymentDateOfEntry is null
	and paymentamount is not null
 
 
--- filter out blank line if there is a non blank line
select distinct appointmentsID
into #dupFilter
from #temp
group by appointmentsID
having count(appointmentsID) > 1
 
delete #temp
where appointmentsID in (select * from #dupfilter)
	and paymentamount is null
 
update #temp 
set paymentamount = 0
where paymentamount is null 
 
SELECT	*
FROM 	#temp
WHERE
	 --Filter on patient copayments
	(
	(1 = 1) OR
	(1 = 2 AND PaymentAmount <> 0 ) OR
	(1 = 3 AND PaymentAmount = 0)
	)
ORDER BY
	apptstart
 
drop table #temp, #dupfilter

Select allOpen in new window