Avatar of Jeff S
Jeff SFlag for United States of America asked on

SQL 2005 Query Assistance

I need assistance with the below mentioned SQL. Essentially, what I want to do is delete the patientvisitID (thus not returning the visit to my report) when my end user selects a distinct PatientVisitProcs.ProceduresId. A visit can contain multiple PatientVisitProcs.ProceduresId ' s however if the visit has a value in this, I want to kill off the entire visit. I have tried many things to no avail. Any input or help is appreciated.
SET NOCOUNT ON

CREATE TABLE #Bill
	(
	patientvisitid INT
	)
INSERT #Bill

SELECT DISTINCT PatientVisit.patientvisitid
FROM    
	PatientVisit 
	INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId 
	LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId 
	INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId 
	LEFT JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId
	INNER JOIN Batch ON PatientVisitProcs.BatchId = Batch.BatchId
	LEFT JOIN PatientInsurance ON PatientVisit.CurrentPICarrierId = PatientInsurance.PatientInsuranceId 
	LEFT JOIN InsuranceCarriers ON PatientVisit.CurrentInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId AND PatientInsurance.InsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId	
	LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId
	INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId 
	LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId 
	LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId 
	INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId 
	LEFT JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId 
	LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId 
	LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId 
	LEFT JOIN doctorfacility refdr on PatientVisit.ReferringDoctorID = refdr.doctorfacilityID
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder 
	LEFT JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId

WHERE 	
	(MedLists_1.TableName = 'BillStatus') 
--	AND PatientVisitProcs.ProceduresId NOT IN ('172')
	AND PatientVisitProcsAgg.InsPayment = 0  
	AND PatientVisitProcsAgg.PatPayment = 0
	AND  --Filter on Date
	(
	(2 = 1 AND (PatientVisit.Entered >= ISNULL('12/01/2010', '1/1/1900') AND PatientVisit.Entered < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000')))) OR
	(2 = 2 AND (PatientVisitProcs.DateOfServiceFrom>= ISNULL('12/01/2010', '1/1/1900') AND PatientVisitProcs.DateOfServiceFrom < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000'))))
	)
	
SELECT 
	PatientVisit.PatientVisitId,
	PatientVisit.TicketNumber AS [Ticket Number], 
    PatientVisitProcs.DateOfServiceFrom AS [Date Of Service], 
	PatientVisit.LastFiledDate, 
	DoctorFacility.ListName AS Doctor, 
	ISNULL(refdr.listname,'No Referring Phys') AS [Referring Physician], 
	ISNULL(InsuranceCarriers.ListName, '') AS [Insurance Carrier], 
	ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')AS [Ins Address],
	dbo.FormatPhone(InsuranceCarriers.Phone1,'')AS InsPhone1,
	dbo.FormatPhone(InsuranceCarriers.Phone2,'')AS InsPhone2,
	ISNULL(PatientInsurance.InsuredId , '') as [Member ID #],
	ISNULL(PatientInsurance.GroupId , '') as [Group ID #],
	dbo.FormatName(PatientProfile.Prefix, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last, PatientProfile.Suffix) AS [Patient Name], 
	patientprofile.PatientId, 
	patientprofile.PatientProfileID, 
	ISNULL(patientprofile.medicalrecordnumber,'No MRN#')as [Medical Record Number],
	ISNULL(patientprofile.address1,'') + '' + ISNULL(patientprofile.address2, '') + ' ' + ISNULL(patientprofile.city,'') + ' ' + ISNULL(patientprofile.state,'') + ' ' + ISNULL(patientprofile.zip,'') AS [Patient Address],
	ISNULL(patientprofile.Phone1,'No Phone')as PatPhone,
	ISNULL(Convert(VarChar(20), patientprofile.birthdate, 101),'No DOB')as PatientDOB,
	FLOOR(DATEDIFF(DAY, patientprofile.birthdate, GETDATE()) / 365.25) AS [Patient Age],
	ISNULL(patientprofile.sex,'')as PatSex,
	PatientVisit.Entered AS [Date Of Entry], 
	DoctorFacility_1.ListName AS Facility, 
    MedLists_1.Description AS [Visit Status], 
	PatientVisitProcs.TotalFee AS Fee, 
	PatientVisitProcs.Code AS [CPT Code], 
	PatientVisitProcs.ProceduresId, 
	CAST(PatientVisitProcs.Notes as VARCHAR(8000))AS [CPT Notes],
	PatientVisitProcs.Units AS Units,
	MedLists_2.Code AS PlaceOfService, 
	MedLists_3.Code AS TypeOfService, 
	ISNULL(CONVERT(varchar(4), MedLists_4.Code), ' ') 
	+ ' ' + ISNULL(CONVERT(varchar(4), MedLists_5.Code), ' ') 
	+ ' ' + ISNULL(CONVERT(varchar(4), MedLists_6.Code), ' ')
	+ ' ' + ISNULL(CONVERT(varchar(4), MedLists_7.Code), ' ') AS Modifier, 
	PatientVisitProcsAgg.InsPayment, 
	PatientVisitProcsAgg.PatPayment, 
	PatientVisitProcsAgg.InsAdjustment, 
	PatientVisitProcsAgg.PatAdjustment, 
	PatientVisitProcsAgg.InsBalance, 
	PatientVisitProcsAgg.PatBalance,
	PatientVisitProcsAgg.InsBalance + PatientVisitProcsAgg.PatBalance AS TotalBalance, 	
	MedLists_8.Description AS Department,
	DoctorFacility_2.ListName AS Company, 
	ISNULL(PatientVisitDiags_0.Code,'') AS Diag1, 
	ISNULL(PatientVisitDiags_1.Code,'') AS Diag2, 
	ISNULL(PatientVisitDiags_2.Code,'') AS Diag3, 
	ISNULL(PatientVisitDiags_3.Code,'') AS Diag4,
	ISNULL(PatientVisitDiags_4.Code,'') AS Diag5,
	ISNULL(PatientVisitDiags_5.Code,'') AS Diag6,
	ISNULL(PatientVisitDiags_6.Code,'') AS Diag7,
	ISNULL(PatientVisitDiags_7.Code,'') AS Diag8
INTO #Tmp

FROM    
	PatientVisit 
	INNER JOIN #Bill b ON PatientVisit.PatientVisitId = b.PatientVisitId
	INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId 
	LEFT JOIN PatientInsurance ON PatientVisit.CurrentPICarrierId = PatientInsurance.PatientInsuranceId 
	LEFT JOIN InsuranceCarriers ON PatientVisit.CurrentInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId AND PatientInsurance.InsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId	
	LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId
	LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId 
	INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId 
	LEFT JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId 
	INNER JOIN Batch ON PatientVisitProcs.BatchId = Batch.BatchId
	INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId 
	LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId 
	LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId 
	INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId 
	LEFT JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId 
	LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId 
	LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId 
	LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId 
	INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId 
	LEFT JOIN doctorfacility refdr on PatientVisit.ReferringDoctorID = refdr.doctorfacilityID
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder 
	LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder 
	LEFT JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId

WHERE  
	(MedLists_1.TableName = 'BillStatus')
	
--DELETE FROM #Tmp WHERE ProceduresId IN ('172')

SELECT * FROM #Tmp

WHERE 	
	 --Filter on Age
	(
	([Patient Age] >= ('-1') AND [Patient Age] <= ('125'))	
	)
	AND --- Filter on Last Filed Date
	[LastFiledDate] >= ISNULL(NULL,'1/1/1900') AND 
    [LastFiledDate] < dateadd(day,1,ISNULL(NULL,'1/1/3000'))
	OR [LastFiledDate] IS NULL
			
ORDER BY 
	[Patient Name], 
	[Ticket Number]

DROP TABLE #Tmp
DROP TABLE #Bill

Open in new window

Microsoft SQL ServerMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Jeff S

8/22/2022 - Mon
Paul_Harris_Fusion

I'm not entirely sure I have understood correctly but....

Delete from #Bill B
Where EXISTS
(Select 1 from TMP T
 where B.patientvisitid = T.PatientVisitId
 and T.ProceduresId in ('172')
)
ASKER
Jeff S

I think you have the idea - getting this though:

Msg 170, Level 15, State 1, Line 145
Line 145: Incorrect syntax near 'B'.
SET NOCOUNT ON

CREATE TABLE #Bill ( patientvisitid INT )
INSERT  #Bill
        SELECT DISTINCT
                PatientVisit.patientvisitid
        FROM    PatientVisit
                INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId
                LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId
                INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
                LEFT JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId
                INNER JOIN Batch ON PatientVisitProcs.BatchId = Batch.BatchId
                LEFT JOIN PatientInsurance ON PatientVisit.CurrentPICarrierId = PatientInsurance.PatientInsuranceId
                LEFT JOIN InsuranceCarriers ON PatientVisit.CurrentInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId AND PatientInsurance.InsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId
                LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId
                INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId
                LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId
                LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId
                INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId
                LEFT JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId
                LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId
                INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId
                LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId
                LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId
                LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId
                LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId
                INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId
                LEFT JOIN doctorfacility refdr on PatientVisit.ReferringDoctorID = refdr.doctorfacilityID
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder
                LEFT JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
        WHERE   ( MedLists_1.TableName = 'BillStatus' )
                AND PatientVisitProcsAgg.InsPayment = 0
                AND PatientVisitProcsAgg.PatPayment = 0
                AND  --Filter on Date
				(
				(2 = 1 AND (PatientVisit.Entered >= ISNULL('12/01/2010', '1/1/1900') AND PatientVisit.Entered < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000')))) OR
				(2 = 2 AND (PatientVisitProcs.DateOfServiceFrom>= ISNULL('12/01/2010', '1/1/1900') AND PatientVisitProcs.DateOfServiceFrom < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000'))))
				)
	
SELECT  PatientVisit.PatientVisitId,
        PatientVisit.TicketNumber AS [Ticket Number],
        PatientVisitProcs.DateOfServiceFrom AS [Date Of Service],
        PatientVisit.LastFiledDate,
        DoctorFacility.ListName AS Doctor,
        ISNULL(refdr.listname, 'No Referring Phys') AS [Referring Physician],
        ISNULL(InsuranceCarriers.ListName, '') AS [Insurance Carrier],
        ISNULL(InsuranceCarriers.Address1, '') + ' '
        + ISNULL(InsuranceCarriers.Address2, '') + ' '
        + ISNULL(InsuranceCarriers.City, '') + ' '
        + ISNULL(InsuranceCarriers.State, '') + ' '
        + ISNULL(InsuranceCarriers.Zip, '') AS [Ins Address],
        dbo.FormatPhone(InsuranceCarriers.Phone1, '') AS InsPhone1,
        dbo.FormatPhone(InsuranceCarriers.Phone2, '') AS InsPhone2,
        ISNULL(PatientInsurance.InsuredId, '') as [Member ID #],
        ISNULL(PatientInsurance.GroupId, '') as [Group ID #],
        dbo.FormatName(PatientProfile.Prefix, PatientProfile.First,
                       PatientProfile.Middle, PatientProfile.Last,
                       PatientProfile.Suffix) AS [Patient Name],
        patientprofile.PatientId,
        patientprofile.PatientProfileID,
        ISNULL(patientprofile.medicalrecordnumber, 'No MRN#') as [Medical Record Number],
        ISNULL(patientprofile.address1, '') + ''
        + ISNULL(patientprofile.address2, '') + ' '
        + ISNULL(patientprofile.city, '') + ' ' + ISNULL(patientprofile.state,'') + ' '
        + ISNULL(patientprofile.zip, '') AS [Patient Address],
        ISNULL(patientprofile.Phone1, 'No Phone') as PatPhone,
        ISNULL(Convert(VarChar(20), patientprofile.birthdate, 101), 'No DOB') as PatientDOB,
        FLOOR(DATEDIFF(DAY, patientprofile.birthdate, GETDATE()) / 365.25) AS [Patient Age],
        ISNULL(patientprofile.sex, '') as PatSex,
        PatientVisit.Entered AS [Date Of Entry],
        DoctorFacility_1.ListName AS Facility,
        MedLists_1.Description AS [Visit Status],
        PatientVisitProcs.TotalFee AS Fee,
        PatientVisitProcs.Code AS [CPT Code],
        PatientVisitProcs.ProceduresId,
        CAST(PatientVisitProcs.Notes as VARCHAR(8000)) AS [CPT Notes],
        PatientVisitProcs.Units AS Units,
        MedLists_2.Code AS PlaceOfService,
        MedLists_3.Code AS TypeOfService,
        ISNULL(CONVERT(varchar(4), MedLists_4.Code), ' ') + ' '
        + ISNULL(CONVERT(varchar(4), MedLists_5.Code), ' ') + ' '
        + ISNULL(CONVERT(varchar(4), MedLists_6.Code), ' ') + ' '
        + ISNULL(CONVERT(varchar(4), MedLists_7.Code), ' ') AS Modifier,
        PatientVisitProcsAgg.InsPayment,
        PatientVisitProcsAgg.PatPayment,
        PatientVisitProcsAgg.InsAdjustment,
        PatientVisitProcsAgg.PatAdjustment,
        PatientVisitProcsAgg.InsBalance,
        PatientVisitProcsAgg.PatBalance,
        PatientVisitProcsAgg.InsBalance + PatientVisitProcsAgg.PatBalance AS TotalBalance,
        MedLists_8.Description AS Department,
        DoctorFacility_2.ListName AS Company,
        ISNULL(PatientVisitDiags_0.Code, '') AS Diag1,
        ISNULL(PatientVisitDiags_1.Code, '') AS Diag2,
        ISNULL(PatientVisitDiags_2.Code, '') AS Diag3,
        ISNULL(PatientVisitDiags_3.Code, '') AS Diag4,
        ISNULL(PatientVisitDiags_4.Code, '') AS Diag5,
        ISNULL(PatientVisitDiags_5.Code, '') AS Diag6,
        ISNULL(PatientVisitDiags_6.Code, '') AS Diag7,
        ISNULL(PatientVisitDiags_7.Code, '') AS Diag8
INTO    #Tmp
FROM    PatientVisit
        INNER JOIN #Bill b ON PatientVisit.PatientVisitId = b.PatientVisitId
        INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId
        LEFT JOIN PatientInsurance ON PatientVisit.CurrentPICarrierId = PatientInsurance.PatientInsuranceId
        LEFT JOIN InsuranceCarriers ON PatientVisit.CurrentInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId AND PatientInsurance.InsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId
        LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId
        LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId
        INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
        LEFT JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId
        INNER JOIN Batch ON PatientVisitProcs.BatchId = Batch.BatchId
        INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId
        LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId
        LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId
        INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId
        LEFT JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId
        LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId
        INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId
        LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId
        LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId
        LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId
        LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId
        INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId
        LEFT JOIN doctorfacility refdr on PatientVisit.ReferringDoctorID = refdr.doctorfacilityID
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder
        LEFT JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
WHERE   ( MedLists_1.TableName = 'BillStatus' )
	
DELETE FROM #Bill B
WHERE EXISTS 
(
SELECT  1
FROM    TMP T
WHERE   B.patientvisitid = T.PatientVisitId
        and T.ProceduresId in ( '172' )
) 

SELECT  *
FROM    #Tmp
WHERE   --Filter on Age
        ( ([Patient Age] >= ( '-1' )
          AND [Patient Age] <= ( '125' ))
        )
        AND --- Filter on Last Filed Date
        [LastFiledDate] >= ISNULL(NULL, '1/1/1900')
        AND [LastFiledDate] < dateadd(day, 1, ISNULL(NULL, '1/1/3000'))
        OR [LastFiledDate] IS NULL
ORDER BY [Patient Name],
        [Ticket Number]

DROP TABLE #Tmp
DROP TABLE #Bill

Open in new window

Paul_Harris_Fusion

Sorry - been spending too much time in Oracle.   SQL server does not let you alias the table you are deleting from.


Delete from #Bill
Where EXISTS
(Select 1 from TMP T
 where PatientVisitId = #Bill.patientvisitid
 and ProceduresId in ('172')
)

alternatively try this

DELETE FROM #Bill
Where patientvisitid
in
(
  SELECT DISTINCT PatientVisitId  
  FROM TMP T
  WHERE ProceduresId in ('172')
)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
Jeff S

I would have expected to see my visit deleted from my dataset however, It is still there. I must have it in the wrong order or placed it wrong in my overall Query?
SET NOCOUNT ON

CREATE TABLE #Bill ( patientvisitid INT )
INSERT  #Bill
        SELECT DISTINCT
                PatientVisit.patientvisitid
        FROM    PatientVisit
                INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId
                LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId
                INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
                LEFT JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId
                INNER JOIN Batch ON PatientVisitProcs.BatchId = Batch.BatchId
                LEFT JOIN PatientInsurance ON PatientVisit.CurrentPICarrierId = PatientInsurance.PatientInsuranceId
                LEFT JOIN InsuranceCarriers ON PatientVisit.CurrentInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId AND PatientInsurance.InsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId
                LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId
                INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId
                LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId
                LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId
                INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId
                LEFT JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId
                LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId
                INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId
                LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId
                LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId
                LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId
                LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId
                INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId
                LEFT JOIN doctorfacility refdr on PatientVisit.ReferringDoctorID = refdr.doctorfacilityID
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder
                LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder
                LEFT JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
        WHERE   ( MedLists_1.TableName = 'BillStatus' )
                AND PatientVisitProcsAgg.InsPayment = 0
                AND PatientVisitProcsAgg.PatPayment = 0
                AND  --Filter on Date
				(
				(2 = 1 AND (PatientVisit.Entered >= ISNULL('12/01/2010', '1/1/1900') AND PatientVisit.Entered < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000')))) OR
				(2 = 2 AND (PatientVisitProcs.DateOfServiceFrom>= ISNULL('12/01/2010', '1/1/1900') AND PatientVisitProcs.DateOfServiceFrom < DATEADD(d,1,ISNULL('12/31/2010','1/1/3000'))))
				)
	
SELECT  PatientVisit.PatientVisitId,
        PatientVisit.TicketNumber AS [Ticket Number],
        PatientVisitProcs.DateOfServiceFrom AS [Date Of Service],
        PatientVisit.LastFiledDate,
        DoctorFacility.ListName AS Doctor,
        ISNULL(refdr.listname, 'No Referring Phys') AS [Referring Physician],
        ISNULL(InsuranceCarriers.ListName, '') AS [Insurance Carrier],
        ISNULL(InsuranceCarriers.Address1, '') + ' '
        + ISNULL(InsuranceCarriers.Address2, '') + ' '
        + ISNULL(InsuranceCarriers.City, '') + ' '
        + ISNULL(InsuranceCarriers.State, '') + ' '
        + ISNULL(InsuranceCarriers.Zip, '') AS [Ins Address],
        dbo.FormatPhone(InsuranceCarriers.Phone1, '') AS InsPhone1,
        dbo.FormatPhone(InsuranceCarriers.Phone2, '') AS InsPhone2,
        ISNULL(PatientInsurance.InsuredId, '') as [Member ID #],
        ISNULL(PatientInsurance.GroupId, '') as [Group ID #],
        dbo.FormatName(PatientProfile.Prefix, PatientProfile.First,
                       PatientProfile.Middle, PatientProfile.Last,
                       PatientProfile.Suffix) AS [Patient Name],
        patientprofile.PatientId,
        patientprofile.PatientProfileID,
        ISNULL(patientprofile.medicalrecordnumber, 'No MRN#') as [Medical Record Number],
        ISNULL(patientprofile.address1, '') + ''
        + ISNULL(patientprofile.address2, '') + ' '
        + ISNULL(patientprofile.city, '') + ' ' + ISNULL(patientprofile.state,'') + ' '
        + ISNULL(patientprofile.zip, '') AS [Patient Address],
        ISNULL(patientprofile.Phone1, 'No Phone') as PatPhone,
        ISNULL(Convert(VarChar(20), patientprofile.birthdate, 101), 'No DOB') as PatientDOB,
        FLOOR(DATEDIFF(DAY, patientprofile.birthdate, GETDATE()) / 365.25) AS [Patient Age],
        ISNULL(patientprofile.sex, '') as PatSex,
        PatientVisit.Entered AS [Date Of Entry],
        DoctorFacility_1.ListName AS Facility,
        MedLists_1.Description AS [Visit Status],
        PatientVisitProcs.TotalFee AS Fee,
        PatientVisitProcs.Code AS [CPT Code],
        PatientVisitProcs.ProceduresId,
        CAST(PatientVisitProcs.Notes as VARCHAR(8000)) AS [CPT Notes],
        PatientVisitProcs.Units AS Units,
        MedLists_2.Code AS PlaceOfService,
        MedLists_3.Code AS TypeOfService,
        ISNULL(CONVERT(varchar(4), MedLists_4.Code), ' ') + ' '
        + ISNULL(CONVERT(varchar(4), MedLists_5.Code), ' ') + ' '
        + ISNULL(CONVERT(varchar(4), MedLists_6.Code), ' ') + ' '
        + ISNULL(CONVERT(varchar(4), MedLists_7.Code), ' ') AS Modifier,
        PatientVisitProcsAgg.InsPayment,
        PatientVisitProcsAgg.PatPayment,
        PatientVisitProcsAgg.InsAdjustment,
        PatientVisitProcsAgg.PatAdjustment,
        PatientVisitProcsAgg.InsBalance,
        PatientVisitProcsAgg.PatBalance,
        PatientVisitProcsAgg.InsBalance + PatientVisitProcsAgg.PatBalance AS TotalBalance,
        MedLists_8.Description AS Department,
        DoctorFacility_2.ListName AS Company,
        ISNULL(PatientVisitDiags_0.Code, '') AS Diag1,
        ISNULL(PatientVisitDiags_1.Code, '') AS Diag2,
        ISNULL(PatientVisitDiags_2.Code, '') AS Diag3,
        ISNULL(PatientVisitDiags_3.Code, '') AS Diag4,
        ISNULL(PatientVisitDiags_4.Code, '') AS Diag5,
        ISNULL(PatientVisitDiags_5.Code, '') AS Diag6,
        ISNULL(PatientVisitDiags_6.Code, '') AS Diag7,
        ISNULL(PatientVisitDiags_7.Code, '') AS Diag8
INTO    #Tmp
FROM    PatientVisit
        INNER JOIN #Bill b ON PatientVisit.PatientVisitId = b.PatientVisitId
        INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId
        LEFT JOIN PatientInsurance ON PatientVisit.CurrentPICarrierId = PatientInsurance.PatientInsuranceId
        LEFT JOIN InsuranceCarriers ON PatientVisit.CurrentInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId AND PatientInsurance.InsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId
        LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId
        LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId
        INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId
        LEFT JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId
        INNER JOIN Batch ON PatientVisitProcs.BatchId = Batch.BatchId
        INNER JOIN MedLists MedLists_1 ON PatientVisit.BillStatus = MedLists_1.JoinId
        LEFT JOIN MedLists MedLists_2 ON PatientVisitProcs.PlaceOfServiceMId = MedLists_2.MedListsId
        LEFT JOIN MedLists MedLists_3 ON PatientVisitProcs.TypeOfServiceMId = MedLists_3.MedListsId
        INNER JOIN PatientVisitAgg ON PatientVisit.PatientVisitId = PatientVisitAgg.PatientVisitId
        LEFT JOIN Procedures ON PatientVisitProcs.ProceduresId = Procedures.ProceduresId
        LEFT JOIN MedLists MedLists_8 ON Procedures.DepartmentMId = MedLists_8.MedListsId
        INNER JOIN DoctorFacility DoctorFacility_1 ON PatientVisit.FacilityId = DoctorFacility_1.DoctorFacilityId
        LEFT OUTER JOIN MedLists MedLists_7 ON PatientVisitProcs.Modifier4MId = MedLists_7.MedListsId
        LEFT OUTER JOIN MedLists MedLists_6 ON PatientVisitProcs.Modifier3MId = MedLists_6.MedListsId
        LEFT OUTER JOIN MedLists MedLists_5 ON PatientVisitProcs.Modifier2MId = MedLists_5.MedListsId
        LEFT OUTER JOIN MedLists MedLists_4 ON PatientVisitProcs.Modifier1MId = MedLists_4.MedListsId
        INNER JOIN DoctorFacility DoctorFacility_2 ON PatientVisit.CompanyId = DoctorFacility_2.DoctorFacilityId
        LEFT JOIN doctorfacility refdr on PatientVisit.ReferringDoctorID = refdr.doctorfacilityID
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_0 ON PatientVisit.PatientVisitId = PatientVisitDiags_0.PatientVisitId AND PatientVisitProcs.PatientVisitDiags1 = PatientVisitDiags_0.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_1 ON PatientVisit.PatientVisitId = PatientVisitDiags_1.PatientVisitId AND PatientVisitProcs.PatientVisitDiags2 = PatientVisitDiags_1.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_2 ON PatientVisit.PatientVisitId = PatientVisitDiags_2.PatientVisitId AND PatientVisitProcs.PatientVisitDiags3 = PatientVisitDiags_2.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_3 ON PatientVisit.PatientVisitId = PatientVisitDiags_3.PatientVisitId AND PatientVisitProcs.PatientVisitDiags4 = PatientVisitDiags_3.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_4 ON PatientVisit.PatientVisitId = PatientVisitDiags_4.PatientVisitId AND PatientVisitProcs.PatientVisitDiags5 = PatientVisitDiags_4.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_5 ON PatientVisit.PatientVisitId = PatientVisitDiags_5.PatientVisitId AND PatientVisitProcs.PatientVisitDiags6 = PatientVisitDiags_5.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_6 ON PatientVisit.PatientVisitId = PatientVisitDiags_6.PatientVisitId AND PatientVisitProcs.PatientVisitDiags7 = PatientVisitDiags_6.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_7 ON PatientVisit.PatientVisitId = PatientVisitDiags_7.PatientVisitId AND PatientVisitProcs.PatientVisitDiags8 = PatientVisitDiags_7.ListOrder
        LEFT OUTER JOIN PatientVisitDiags PatientVisitDiags_8 ON PatientVisit.PatientVisitId = PatientVisitDiags_8.PatientVisitId AND PatientVisitProcs.PatientVisitDiags9 = PatientVisitDiags_8.ListOrder
        LEFT JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
WHERE   ( MedLists_1.TableName = 'BillStatus' )
	
DELETE FROM #Bill 
WHERE patientvisitid 
IN
(
  SELECT DISTINCT PatientVisitId  
  FROM #TMP T
  WHERE ProceduresId in (172)
) 

SELECT  *
FROM    #Tmp
WHERE   --Filter on Age
        ( ([Patient Age] >= ( '-1' )
          AND [Patient Age] <= ( '125' ))
        )
        AND --- Filter on Last Filed Date
        [LastFiledDate] >= ISNULL(NULL, '1/1/1900')
        AND [LastFiledDate] < dateadd(day, 1, ISNULL(NULL, '1/1/3000'))
        OR [LastFiledDate] IS NULL
        
ORDER BY [Patient Name],
        [Ticket Number]

DROP TABLE #Tmp
DROP TABLE #Bill

Open in new window

ASKER CERTIFIED SOLUTION
Paul_Harris_Fusion

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Jeff S

THANKS