Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Visit Balance > 0 help 1

Status
Not open for further replies.
jeffsturgeon2002

jeffsturgeon2002

Programmer
Sep 19, 2006
136
US
I need this report to report back only the VititBalances greater than $0.00

I know it needs to go into my "Where", but not to sure how to since I made this field from two fields InsBalance plus PatBalance. Any assistance is appreciated.

Code: 
[COLOR=RED]patientVisitagg.[InsBalance]+ patientvisitagg.[PatBalance] AS VisitBalance,[/COLOR]

FROM

Code: 
/*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'))))
	)

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], 
        CONVERT(VARCHAR,PatientVisitProcs.DateOfServiceFrom,101) 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, 
	[COLOR=red]patientVisitagg.[InsBalance]+ patientvisitagg.[PatBalance] AS VisitBalance,[/color]
	--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')

ORDER BY [TicketNumber]

DROP TABLE #Bill

Jeff

SELECT * FROM users WHERE clue > 0
 
Sort by date Sort by votes
There are too many NULLS in the where clause for me to make any sense out of it. However, you can do 'math' even in a where clause.

Ex:

[tt][blue]
Where (patientVisitagg.[InsBalance]+ patientvisitagg.[PatBalance]) > 0
And Null Is NULL
Or Null Is Not NULL
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom
 
Upvote 0 Downvote
George,

I hate it when I make it more difficult than what it really is!! :)

Thanks for your help!

The NULL is NULL and NOT NULL mess is due to parameter fields --- If you saw it in the actual product, it would look like this ...

Code: 
/*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
	(
	(?CPTCODE.ITEMDATA? IS NOT NULL AND PatientVisitProcs.ProceduresId IN (?CPTCODE.ITEMDATA.U?)) OR
	(?CPTCODE.ITEMDATA? IS NULL)
	)
	AND  --Filter on Modifier
	(
	((?MODIFIERS.ITEMDATA? IS NOT NULL AND PatientVisitProcs.Modifier1MId IN (?MODIFIERS.ITEMDATA.U?)) OR
	(?MODIFIERS.ITEMDATA? IS NOT NULL AND PatientVisitProcs.Modifier2MId IN (?MODIFIERS.ITEMDATA.U?)) OR
	(?MODIFIERS.ITEMDATA? IS NOT NULL AND PatientVisitProcs.Modifier3MId IN (?MODIFIERS.ITEMDATA.U?)) OR
	(?MODIFIERS.ITEMDATA? IS NOT NULL AND PatientVisitProcs.Modifier4MId IN (?MODIFIERS.ITEMDATA.U?)) OR
	(?MODIFIERS.ITEMDATA? IS NULL AND ?ALLMODIFIERS.VALUE? = '1')) OR
	(?ALLMODIFIERS.VALUE? = '2' AND
	 ?MODIFIERS.ITEMDATA? IS NULL
	) OR
	(?ALLMODIFIERS.VALUE? = '2' AND
	 ?MODIFIERS.ITEMDATA? = '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
	(
	(?DOCTOR.ITEMDATA? IS NOT NULL AND PatientVisit.DoctorID IN (?DOCTOR.ITEMDATA.U?)) OR
	(?DOCTOR.ITEMDATA? IS NULL)
	)
	AND  --Filter on Diagnosis
	(
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_0.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_1.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_2.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_3.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_4.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_5.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_6.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_7.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NOT NULL AND PatientVisitDiags_8.DiagnosisId IN (?DIAGNOSIS.ITEMDATA.U?)) OR 
	(?DIAGNOSIS.ITEMDATA? IS NULL)
	)
	AND  --Filter on company
	(
	(?COMPANY.ITEMDATA? IS NOT NULL AND PatientVisit.CompanyID IN (?COMPANY.ITEMDATA.U?)) OR
	(?COMPANY.ITEMDATA? IS NULL)
	)
	AND  --Filter on facility
	(
	(?FACILITY.ITEMDATA? IS NOT NULL AND PatientVisit.FacilityID IN (?FACILITY.ITEMDATA.U?)) OR
	(?FACILITY.ITEMDATA? IS NULL)
	)
	AND  --Filter on Department
	(
	(?DEPARTMENT.ITEMDATA? IS NOT NULL AND Procedures.DepartmentMId IN (?DEPARTMENT.ITEMDATA.U?)) OR
	(?DEPARTMENT.ITEMDATA? IS NULL)
	)
	AND  --Filter on Type Of Service
	(
	(?TYPEOFSERVICE.ITEMDATA? IS NOT NULL AND PatientVisitProcs.TypeOfServiceMId IN (?TYPEOFSERVICE.ITEMDATA.U?)) OR
	(?TYPEOFSERVICE.ITEMDATA? IS NULL)
	)
	AND  --Filter on Place Of Service
	(
	(?PLACEOFSERVICE.ITEMDATA? IS NOT NULL AND PatientVisitProcs.PlaceOfServiceMId IN (?PLACEOFSERVICE.ITEMDATA.U?)) OR
	(?PLACEOFSERVICE.ITEMDATA? IS NULL)
	)
	AND  --Filter on Financial Class
	(
	(?FINANCIALCLASS.ITEMDATA? IS NOT NULL AND PatientVisit.FinancialClassMId IN (?FINANCIALCLASS.ITEMDATA.U?)) OR
	(?FINANCIALCLASS.ITEMDATA? IS NULL)
	)
	AND  --Filter on Insurance Carrier
	(
	(?INSURANCECARRIER.ITEMDATA? IS NOT NULL AND PatientVisit.PrimaryInsuranceCarriersId IN (?INSURANCECARRIER.ITEMDATA.U?)) OR
	(?INSURANCECARRIER.ITEMDATA? IS NULL)
	)
	AND  --Filter on Insurance Group
	(
	(?INSURANCEGROUP.ITEMDATA? IS NOT NULL AND InsuranceCarriers.InsuranceGroupId IN (?INSURANCEGROUP.ITEMDATA.U?)) OR
	(?INSURANCEGROUP.ITEMDATA? IS NULL)
	)
	AND  --Filter on Date
	(
	(?DATETYPE.VALUE.U? = 1 AND (PatientVisit.Entered >= ISNULL(?DATERANGE.DATE1?, '1/1/1900') AND PatientVisit.Entered < DATEADD(d,1,ISNULL(?DATERANGE.DATE2?,'1/1/3000'))))                 OR
	(?DATETYPE.VALUE.U? = 2 AND (PatientVisitProcs.DateOfServiceFrom>= ISNULL(?DATERANGE.DATE1?, '1/1/1900') AND PatientVisitProcs.DateOfServiceFrom <                                                                DATEADD(d,1,ISNULL(?DATERANGE.DATE2?,'1/1/3000'))))
	)

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')

DROP TABLE #Bill

Jeff

SELECT * FROM users WHERE clue > 0
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
212
f0rg3tfu1
f0rg3tfu1
grnzbra
  • Locked
  • Question
Is &quot;IF EXISTS()...&quot; better than &quot;IF (SELECT COUNT...) &gt; 0&quot;
Replies
4
Views
1K
gmmastros
gmmastros
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
534
Chris Miller
Chris Miller
bmacbmac
  • Locked
  • Question
Help searching within this Json file
Replies
0
Views
77
bmacbmac
bmacbmac
G12Consult
  • Locked
  • Question
Help with some code
Replies
2
Views
108
SaltyTheFrog
SaltyTheFrog

Part and Inventory Search

Sponsor

Back
Top