Not returning Results right - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.
All Forums
SQL Server 2000 Forums
Transact-SQL (2000)
Not returning Results right
Author
Topic
JeffS23
Posting Yak Master
212 Posts
Posted - 2007-03-07 : 17:39:08
Issue - I need this report to return any patient where the following CPT codes are billed (99201 - 99205) and if they had other procedures billed, return them along with the (99201-99205). Currently, this is only returning the patients that had a 99201-99205 billed only. If I add in another procedure, they drop off the result set. As long as one of the codes is between 99201-99205, I want it to result back. Make sense?

Code below:
/*Billing Analysis by Patient*/
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 InsuranceCarriers ON PatientVisit.PrimaryInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId 
	LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId 
	LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId 
	INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId 
	INNER JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId 
	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 
	INNER 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 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 
	INNER JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
WHERE     (MedLists_1.TableName = 'BillStatus')
	AND  --Filter on CPT Code
	(
	(NULL IS NOT NULL AND PatientVisitProcs.ProceduresId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Modifier
	(
	((NULL IS NOT NULL AND PatientVisitProcs.Modifier1MId IN (NULL)) OR
	(NULL IS NOT NULL AND PatientVisitProcs.Modifier2MId IN (NULL)) OR
	(NULL IS NOT NULL AND PatientVisitProcs.Modifier3MId IN (NULL)) OR
	(NULL IS NOT NULL AND PatientVisitProcs.Modifier4MId IN (NULL)) OR
	(NULL IS NULL AND '1' = '1')) OR
	('1' = '2' AND
	 NULL IS NULL
	) OR
	('1' = '2' AND
	 NULL = '0' AND
	 PatientVisitProcs.Modifier1MId IS NULL AND
	 PatientVisitProcs.Modifier2MId IS NULL AND
	 PatientVisitProcs.Modifier3MId IS NULL AND
	 PatientVisitProcs.Modifier4MId IS NULL
	)
	)
	AND  --Filter on doctor
	(
	(NULL IS NOT NULL AND PatientVisit.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Diagnosis
	(
	(NULL IS NOT NULL AND PatientVisitDiags_0.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_1.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_2.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_3.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_4.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_5.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_6.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_7.DiagnosisId IN (NULL)) OR 
	(NULL IS NOT NULL AND PatientVisitDiags_8.DiagnosisId IN (NULL)) OR 
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND PatientVisit.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on facility
	(
	(NULL IS NOT NULL AND PatientVisit.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Department
	(
	(NULL IS NOT NULL AND Procedures.DepartmentMId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Type Of Service
	(
	(NULL IS NOT NULL AND PatientVisitProcs.TypeOfServiceMId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Place Of Service
	(
	(NULL IS NOT NULL AND PatientVisitProcs.PlaceOfServiceMId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Financial Class
	(
	(NULL IS NOT NULL AND PatientVisit.FinancialClassMId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Insurance Carrier
	(
	(NULL IS NOT NULL AND PatientVisit.PrimaryInsuranceCarriersId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Insurance Group
	(
	(NULL IS NOT NULL AND InsuranceCarriers.InsuranceGroupId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on Date
	(
	(1 = 1 AND (PatientVisit.Entered >= ISNULL(NULL, '1/1/1900') AND PatientVisit.Entered < DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))                 OR
	(1 = 2 AND (PatientVisitProcs.DateOfServiceFrom>= ISNULL(NULL, '1/1/1900') AND PatientVisitProcs.DateOfServiceFrom <                                                                DATEADD(d,1,ISNULL(NULL,'1/1/3000'))))
	)

delete b from #bill b
join PatientVisitProcs pvp on b.patientvisitid = pvp.patientvisitid
where pvp.CPTCode NOT IN ('99201','99202','99203','99204','99205')

SELECT	DoctorFacility.ListName AS Doctor, 
	ISNULL(InsuranceCarriers.ListName, '') AS [Insurance Carrier], 
	MedLists.Description AS [Financial Class], 
	ISNULL(InsuranceGroup.Name, '') AS [Insurance Group], 
	dbo.FormatName(PatientProfile.Prefix, PatientProfile.First, PatientProfile.Middle, PatientProfile.Last, PatientProfile.Suffix) AS [Patient Name], 
	PatientVisit.TicketNumber AS [Ticket Number], 
        PatientVisitProcs.DateOfServiceFrom AS [Date Of Service], 
	PatientVisit.Entered AS [Date Of Entry], 
	DoctorFacility_1.ListName AS Facility, 
        MedLists_1.Description AS [Visit Status], 
	PatientVisitProcs.TotalFee AS Fee, 
	PatientVisitProcs.CPTCode AS [CPT Code], 
	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,
	ISNULL(PatientVisitDiags_8.Code,'') AS Diag9
FROM    PatientVisit 
	INNER JOIN #Bill b ON PatientVisit.PatientVisitId = b.PatientVisitId
	INNER JOIN DoctorFacility ON PatientVisit.DoctorId = DoctorFacility.DoctorFacilityId 
	LEFT JOIN InsuranceCarriers ON PatientVisit.PrimaryInsuranceCarriersId = InsuranceCarriers.InsuranceCarriersId 
	LEFT JOIN MedLists ON PatientVisit.FinancialClassMId = MedLists.MedListsId 
	LEFT JOIN InsuranceGroup ON InsuranceCarriers.InsuranceGroupId = InsuranceGroup.InsuranceGroupId 
	INNER JOIN PatientProfile ON PatientVisit.PatientProfileId = PatientProfile.PatientProfileId 
	INNER JOIN PatientVisitProcs ON PatientVisit.PatientVisitId = PatientVisitProcs.PatientVisitId 
	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 
	INNER 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 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 
	INNER JOIN PatientVisitProcsAgg ON PatientVisitProcs.PatientVisitProcsId = PatientVisitProcsAgg.PatientVisitProcsId
WHERE     (MedLists_1.TableName = 'BillStatus')
	AND PatientVisitProcs.CPTCode IN ('99201','99202','99203','99204','99205')
DROP TABLE #Bill
SwePeso
Patron Saint of Lost Yaks
30421 Posts
Posted - 2007-03-07 : 17:59:03
All these LEFT JOINs, are they really necessary?

Peter Larsson
Helsingborg, Sweden
JeffS23
Posting Yak Master
212 Posts
Posted - 2007-03-07 : 18:06:27
Yes - Which ones arent necessary?!
Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS
SQLTeam.com Weblog via RSS
- Advertisement -
Resources
