?
Solved

SQL Syntax help needed

Posted on 2009-12-17
13
Medium Priority
?
250 Views
Last Modified: 2012-05-08
First and foremost, my SQL is handled dynamically by the server, therefore, coding in my WHERE clauses will look odd to you. Trust me, I know it looks odd, but it is not the issue.

I am getting the following error message and can't see where my issue is.

Msg 156, Level 15, State 1, Line 302
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 313
Incorrect syntax near the keyword 'ORDER'.
Msg 156, Level 15, State 1, Line 325
Incorrect syntax near the keyword 'ORDER'.

Any help is appreciated.
SET NOCOUNT ON

DECLARE @maxamt money,
    	  @minamt money

if 1 = '1'
begin
    	set @minamt = -99999999.00
    	set @maxamt = 999999999.00
end
if 1 = '2'
begin
    	set @minamt = -9999999.00
    	set @maxamt = NULL
end
if 1 = '3'
begin
    	set @minamt = NULL
    	set @maxamt = 99999999.00
end
if 1 = '4'
begin
    	set @minamt = NULL
    	set @maxamt = NULL
end

DECLARE @insmaxamt money,
    	  @insminamt money

if 1 = '1'
begin
    	set @insminamt = -99999999.00
    	set @insmaxamt = 999999999.00
end
if 1 = '2'
begin
    	set @insminamt = -9999999.00
    	set @insmaxamt = NULL
end
if 1 = '3'
begin
    	set @insminamt = NULL
    	set @insmaxamt = 99999999.00
end
if 1 = '4'
begin
    	set @insminamt = NULL
    	set @insmaxamt = NULL
end

DECLARE @totalmaxamt money,
    	  @totalminamt money

if 1 = '1'
begin
    	set @totalminamt = -99999999.00
    	set @totalmaxamt = 999999999.00
end
if 1 = '2'
begin
    	set @totalminamt = -9999999.00
    	set @totalmaxamt = NULL
end
if 1 = '3'
begin
    	set @totalminamt = NULL
    	set @totalmaxamt = 99999999.00
end
if 1 = '4'
begin
    	set @totalminamt = NULL
    	set @totalmaxamt = NULL
end

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 
	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  --Filter on CPT Code
	(
	(NULL IS NOT NULL AND PatientVisitProcs.ProceduresId IN (NULL)) OR
	(NULL IS NULL)
	)
                AND --Filter on BillStatus
	(
                (NULL IS NULL AND '1' = '1' ) OR
	('1' = '2' AND NULL IS NULL)  OR
	PatientVisit.BillStatus IN (NULL)
                )
                AND  --Filter on visitowner
	(
	(NULL IS NOT NULL AND PatientVisit.VisitOwnerMID 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 Referring Doc
	(
	(NULL IS NOT NULL AND PatientVisit.ReferringDoctorID 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 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 facility
	(
	(NULL IS NOT NULL AND PatientVisit.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on pat ____
	(
	(Null IS NOT NULL AND PatientProfile.____ in (Null)) OR
	(Null IS NULL)
	)
	AND  --Filter on Insurance Carrier
	(
	(NULL IS NOT NULL AND PatientVisit.CurrentInsuranceCarriersId 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 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 patient balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.PatBalance <= @maxamt) OR
	(1 = 3 AND PatientVisitAgg.PatBalance >= @minamt) OR
	(1 = 4 AND PatientVisitAgg.PatBalance >= @minamt AND PatientVisitAgg.PatBalance <= @maxamt)
	)
	AND  --Filter on Insurance balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.InsBalance <> 0 AND PatientVisitAgg.InsBalance <= @insmaxamt) OR
	(1 = 3 AND PatientVisitAgg.InsBalance >= @insminamt) OR
	(1 = 4 AND PatientVisitAgg.InsBalance >= @insminamt AND PatientVisitAgg.InsBalance <= @insmaxamt)
	)
	AND  --Filter on Total balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance <= @totalmaxamt) OR
	(1 = 3 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance >= @totalminamt) OR
	(1 = 4 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance >= @totalminamt  AND PatientVisitAgg.InsBalance +  PatientVisitAgg.PatBalance <= @totalmaxamt)
	)
	AND --Filter on patient
	(
	('48' IS NOT NULL AND  PatientProfile.PatientProfileID IN (48)) OR
	('48' 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 
	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 #],
	--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], 
	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],
	'Age @ TOS' = CASE
     	WHEN DATEDIFF(YY,patientprofile.birthdate, patientvisit.visit) <= 0
        	THEN CAST(DATEDIFF(MM,patientprofile.birthdate, patientvisit.visit)as varchar(10))+ ' Month(s)'
        	ELSE CAST(DATEDIFF(YY,patientprofile.birthdate, patientvisit.visit)as varchar(10))+ ' Yr(s)'
       	END,
	ISNULL(patientprofile.____,'')as PatSex,
	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], 
	CAST(PatientVisitProcs.Notes as VARCHAR(8000))AS [CPT Notes],
	(
	SELECT TOP 1
        		CASE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		  WHEN '**long**' THEN CAST(dbo.PatientCorrespondence.DescriptionLong AS VARCHAR(8000))
        		  ELSE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		END AS Notes
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrNotes,
	(
	SELECT Top 1 Created  
	FROM 
	(	
	SELECT TOP 2 PatientCorrespondence.Createdby
	FROM
        PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
	pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
	PatientCorrespondence.Created DESC
	)
	ORDER BY 
	PatientCorrespondence.Created
	)AS CorrNotes2,
	(
	SELECT TOP 1
        		PatientCorrespondence.Created 
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection'
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrDate,
	(
	SELECT TOP 1
        		PatientCorrespondence.Createdby
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrCreatedby,
	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,
	CASE
	WHEN 0 = 1 THEN DoctorFacility.ListName
	WHEN 0 = 2 THEN DoctorFacility_1.ListName
	WHEN 0 = 3 THEN DoctorFacility_2.ListName
	WHEN 0 = 4 THEN ISNULL(InsuranceCarriers.ListName,'No Carrier') + ' ' + ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')
	WHEN 0 = 5 THEN ISNULL(refdr.listname,'No Referring Phys')
	ELSE NULL
	END AS Grouping
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 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')

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

0
Comment
Question by:Jeff S
  • 5
  • 4
  • 2
  • +2
13 Comments
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 664 total points
ID: 26074856
Hi,

One line 302, you need to name the outer table.
Change
PatientCorrespondence.Created DESC
      )
      ORDER BY
      PatientCorrespondence.Created
to
PatientCorrespondence.Created DESC
      ) T
      ORDER BY
      T.Created
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26074899
There is a missing subquery alias on line 301.

Lines 313 and 325 look OK and they could be fixed by above correction.
SET NOCOUNT ON

DECLARE @maxamt money,
    	  @minamt money

if 1 = '1'
begin
    	set @minamt = -99999999.00
    	set @maxamt = 999999999.00
end
if 1 = '2'
begin
    	set @minamt = -9999999.00
    	set @maxamt = NULL
end
if 1 = '3'
begin
    	set @minamt = NULL
    	set @maxamt = 99999999.00
end
if 1 = '4'
begin
    	set @minamt = NULL
    	set @maxamt = NULL
end

DECLARE @insmaxamt money,
    	  @insminamt money

if 1 = '1'
begin
    	set @insminamt = -99999999.00
    	set @insmaxamt = 999999999.00
end
if 1 = '2'
begin
    	set @insminamt = -9999999.00
    	set @insmaxamt = NULL
end
if 1 = '3'
begin
    	set @insminamt = NULL
    	set @insmaxamt = 99999999.00
end
if 1 = '4'
begin
    	set @insminamt = NULL
    	set @insmaxamt = NULL
end

DECLARE @totalmaxamt money,
    	  @totalminamt money

if 1 = '1'
begin
    	set @totalminamt = -99999999.00
    	set @totalmaxamt = 999999999.00
end
if 1 = '2'
begin
    	set @totalminamt = -9999999.00
    	set @totalmaxamt = NULL
end
if 1 = '3'
begin
    	set @totalminamt = NULL
    	set @totalmaxamt = 99999999.00
end
if 1 = '4'
begin
    	set @totalminamt = NULL
    	set @totalmaxamt = NULL
end

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 
	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  --Filter on CPT Code
	(
	(NULL IS NOT NULL AND PatientVisitProcs.ProceduresId IN (NULL)) OR
	(NULL IS NULL)
	)
                AND --Filter on BillStatus
	(
                (NULL IS NULL AND '1' = '1' ) OR
	('1' = '2' AND NULL IS NULL)  OR
	PatientVisit.BillStatus IN (NULL)
                )
                AND  --Filter on visitowner
	(
	(NULL IS NOT NULL AND PatientVisit.VisitOwnerMID 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 Referring Doc
	(
	(NULL IS NOT NULL AND PatientVisit.ReferringDoctorID 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 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 facility
	(
	(NULL IS NOT NULL AND PatientVisit.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on pat ____
	(
	(Null IS NOT NULL AND PatientProfile.____ in (Null)) OR
	(Null IS NULL)
	)
	AND  --Filter on Insurance Carrier
	(
	(NULL IS NOT NULL AND PatientVisit.CurrentInsuranceCarriersId 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 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 patient balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.PatBalance <= @maxamt) OR
	(1 = 3 AND PatientVisitAgg.PatBalance >= @minamt) OR
	(1 = 4 AND PatientVisitAgg.PatBalance >= @minamt AND PatientVisitAgg.PatBalance <= @maxamt)
	)
	AND  --Filter on Insurance balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.InsBalance <> 0 AND PatientVisitAgg.InsBalance <= @insmaxamt) OR
	(1 = 3 AND PatientVisitAgg.InsBalance >= @insminamt) OR
	(1 = 4 AND PatientVisitAgg.InsBalance >= @insminamt AND PatientVisitAgg.InsBalance <= @insmaxamt)
	)
	AND  --Filter on Total balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance <= @totalmaxamt) OR
	(1 = 3 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance >= @totalminamt) OR
	(1 = 4 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance >= @totalminamt  AND PatientVisitAgg.InsBalance +  PatientVisitAgg.PatBalance <= @totalmaxamt)
	)
	AND --Filter on patient
	(
	('48' IS NOT NULL AND  PatientProfile.PatientProfileID IN (48)) OR
	('48' 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 
	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 #],
	--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], 
	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],
	'Age @ TOS' = CASE
     	WHEN DATEDIFF(YY,patientprofile.birthdate, patientvisit.visit) <= 0
        	THEN CAST(DATEDIFF(MM,patientprofile.birthdate, patientvisit.visit)as varchar(10))+ ' Month(s)'
        	ELSE CAST(DATEDIFF(YY,patientprofile.birthdate, patientvisit.visit)as varchar(10))+ ' Yr(s)'
       	END,
	ISNULL(patientprofile.____,'')as PatSex,
	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], 
	CAST(PatientVisitProcs.Notes as VARCHAR(8000))AS [CPT Notes],
	(
	SELECT TOP 1
        		CASE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		  WHEN '**long**' THEN CAST(dbo.PatientCorrespondence.DescriptionLong AS VARCHAR(8000))
        		  ELSE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		END AS Notes
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrNotes,
	(
	SELECT Top 1 Created  
	FROM 
	(	
	SELECT TOP 2 PatientCorrespondence.Createdby
	FROM
        PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
	pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
	PatientCorrespondence.Created DESC
	) pc2
	ORDER BY 
	  pc2.Created
	) AS CorrNotes2,
	(
	SELECT TOP 1
        		PatientCorrespondence.Created 
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection'
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrDate,
	(
	SELECT TOP 1
        		PatientCorrespondence.Createdby
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrCreatedby,
	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,
	CASE
	WHEN 0 = 1 THEN DoctorFacility.ListName
	WHEN 0 = 2 THEN DoctorFacility_1.ListName
	WHEN 0 = 3 THEN DoctorFacility_2.ListName
	WHEN 0 = 4 THEN ISNULL(InsuranceCarriers.ListName,'No Carrier') + ' ' + ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')
	WHEN 0 = 5 THEN ISNULL(refdr.listname,'No Referring Phys')
	ELSE NULL
	END AS Grouping
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 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')

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

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26074909
Too late... :-)
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
LVL 12

Expert Comment

by:cminear
ID: 26074931
With the error referred to on lines 313 and 325, you have this in the WHERE clause just prior to those lines:
      WHERE
            pv1.PatientVisitId = PatientVisit.PatientVisitId

You either want "pv1.PatientVisitId = PatientCorrespondence.PatientVisitId" or "pv1.PatientVisitId = pv1.PatientVisitId".  (Yes, the second option would always be true.)  Because you aliased PatientVisit to pv1, it is a syntax error to then try to use PatientVisit.
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26074937
hehe...at times, it happens due to browser speed as well...but it's fun :-)
0
 
LVL 7

Author Comment

by:Jeff S
ID: 26074975
Am I missing something still? Get this now.

Msg 207, Level 16, State 3, Line 79
Invalid column name '____'.
Msg 207, Level 16, State 3, Line 239
Invalid column name '____'.
Msg 209, Level 16, State 1, Line 239
Ambiguous column name 'Created'.
Msg 207, Level 16, State 3, Line 239
Invalid column name 'Created'.

SET NOCOUNT ON

DECLARE @maxamt money,
    	  @minamt money

if 1 = '1'
begin
    	set @minamt = -99999999.00
    	set @maxamt = 999999999.00
end
if 1 = '2'
begin
    	set @minamt = -9999999.00
    	set @maxamt = NULL
end
if 1 = '3'
begin
    	set @minamt = NULL
    	set @maxamt = 99999999.00
end
if 1 = '4'
begin
    	set @minamt = NULL
    	set @maxamt = NULL
end

DECLARE @insmaxamt money,
    	  @insminamt money

if 1 = '1'
begin
    	set @insminamt = -99999999.00
    	set @insmaxamt = 999999999.00
end
if 1 = '2'
begin
    	set @insminamt = -9999999.00
    	set @insmaxamt = NULL
end
if 1 = '3'
begin
    	set @insminamt = NULL
    	set @insmaxamt = 99999999.00
end
if 1 = '4'
begin
    	set @insminamt = NULL
    	set @insmaxamt = NULL
end

DECLARE @totalmaxamt money,
    	  @totalminamt money

if 1 = '1'
begin
    	set @totalminamt = -99999999.00
    	set @totalmaxamt = 999999999.00
end
if 1 = '2'
begin
    	set @totalminamt = -9999999.00
    	set @totalmaxamt = NULL
end
if 1 = '3'
begin
    	set @totalminamt = NULL
    	set @totalmaxamt = 99999999.00
end
if 1 = '4'
begin
    	set @totalminamt = NULL
    	set @totalmaxamt = NULL
end

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 
	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  --Filter on CPT Code
	(
	(NULL IS NOT NULL AND PatientVisitProcs.ProceduresId IN (NULL)) OR
	(NULL IS NULL)
	)
                AND --Filter on BillStatus
	(
                (NULL IS NULL AND '1' = '1' ) OR
	('1' = '2' AND NULL IS NULL)  OR
	PatientVisit.BillStatus IN (NULL)
                )
                AND  --Filter on visitowner
	(
	(NULL IS NOT NULL AND PatientVisit.VisitOwnerMID 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 Referring Doc
	(
	(NULL IS NOT NULL AND PatientVisit.ReferringDoctorID 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 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 facility
	(
	(NULL IS NOT NULL AND PatientVisit.FacilityID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND --Filter on pat ____
	(
	(Null IS NOT NULL AND PatientProfile.____ in (Null)) OR
	(Null IS NULL)
	)
	AND  --Filter on Insurance Carrier
	(
	(NULL IS NOT NULL AND PatientVisit.CurrentInsuranceCarriersId 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 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 patient balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.PatBalance <= @maxamt) OR
	(1 = 3 AND PatientVisitAgg.PatBalance >= @minamt) OR
	(1 = 4 AND PatientVisitAgg.PatBalance >= @minamt AND PatientVisitAgg.PatBalance <= @maxamt)
	)
	AND  --Filter on Insurance balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.InsBalance <> 0 AND PatientVisitAgg.InsBalance <= @insmaxamt) OR
	(1 = 3 AND PatientVisitAgg.InsBalance >= @insminamt) OR
	(1 = 4 AND PatientVisitAgg.InsBalance >= @insminamt AND PatientVisitAgg.InsBalance <= @insmaxamt)
	)
	AND  --Filter on Total balance
	(
	(1 = 1) OR
	(1 = 2 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance <> 0 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance <= @totalmaxamt) OR
	(1 = 3 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance >= @totalminamt) OR
	(1 = 4 AND PatientVisitAgg.InsBalance + PatientVisitAgg.PatBalance >= @totalminamt  AND PatientVisitAgg.InsBalance +  PatientVisitAgg.PatBalance <= @totalmaxamt)
	)
	AND --Filter on patient
	(
	('48' IS NOT NULL AND  PatientProfile.PatientProfileID IN (48)) OR
	('48' 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 
	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 #],
	--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], 
	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],
	'Age @ TOS' = CASE
     	WHEN DATEDIFF(YY,patientprofile.birthdate, patientvisit.visit) <= 0
        	THEN CAST(DATEDIFF(MM,patientprofile.birthdate, patientvisit.visit)as varchar(10))+ ' Month(s)'
        	ELSE CAST(DATEDIFF(YY,patientprofile.birthdate, patientvisit.visit)as varchar(10))+ ' Yr(s)'
       	END,
	ISNULL(patientprofile.____,'')as PatSex,
	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], 
	CAST(PatientVisitProcs.Notes as VARCHAR(8000))AS [CPT Notes],
	(
	SELECT TOP 1
        		CASE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		  WHEN '**long**' THEN CAST(dbo.PatientCorrespondence.DescriptionLong AS VARCHAR(8000))
        		  ELSE CAST(dbo.PatientCorrespondence.Description AS VARCHAR(8000))
        		END AS Notes
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrNotes,
	(
	SELECT Top 1 Created  
	FROM 
	(	
	SELECT TOP 2 PatientCorrespondence.Createdby
	FROM
        PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
	pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
	PatientCorrespondence.Created DESC
	)T
	ORDER BY 
	T.Created
	)AS CorrNotes2,
	(
	SELECT TOP 1
        		PatientCorrespondence.Created 
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection'
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrDate,
	(
	SELECT TOP 1
        		PatientCorrespondence.Createdby
	FROM
        		PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
		pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
		PatientCorrespondence.Created DESC
	)
	AS CorrCreatedby,
	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,
	CASE
	WHEN 0 = 1 THEN DoctorFacility.ListName
	WHEN 0 = 2 THEN DoctorFacility_1.ListName
	WHEN 0 = 3 THEN DoctorFacility_2.ListName
	WHEN 0 = 4 THEN ISNULL(InsuranceCarriers.ListName,'No Carrier') + ' ' + ISNULL(InsuranceCarriers.Address1,'')+ ' ' + ISNULL(InsuranceCarriers.Address2,'')+ ' ' + ISNULL(InsuranceCarriers.City,'')+ ' ' + ISNULL(InsuranceCarriers.State,'')+ ' ' + ISNULL(InsuranceCarriers.Zip,'')
	WHEN 0 = 5 THEN ISNULL(refdr.listname,'No Referring Phys')
	ELSE NULL
	END AS Grouping
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 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')

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

0
 
LVL 43

Expert Comment

by:pcelba
ID: 26074991
I would say the "pv1.PatientVisitId = PatientVisit.PatientVisitId" is a correlated subquery condition and should be OK.
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26075044
Is it due to scope terminators?
0
 
LVL 7

Author Comment

by:Jeff S
ID: 26075057
I don't know what scope terminators are .... sounds bad though. LOL
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26075069
:-) Going to sleep now...pretty late here...nJoy...
0
 
LVL 43

Expert Comment

by:pcelba
ID: 26075077
Invalid column name '____'.  (You should use some letters in column name)

Line 184:
(Null IS NOT NULL AND PatientProfile.____ in (Null)) OR

Line 267:
ISNULL(patientprofile.____,'')as PatSex,

Created column needs local alias when it is contained in two or more tables in one query
0
 
LVL 4

Accepted Solution

by:
liorfr earned 668 total points
ID: 26075094
Instead of this:
        ORDER BY
      PatientCorrespondence.Created DESC
      )
      ORDER BY
      PatientCorrespondence.Created
      )AS CorrNotes2,

Try this:

        ORDER BY
      PatientCorrespondence.Created DESC
      )AS something
      ORDER BY
      PatientCorrespondence.Created
      )AS CorrNotes2,

Lior
0
 
LVL 43

Assisted Solution

by:pcelba
pcelba earned 668 total points
ID: 26075114
Also this part contains some suspicious column names (you should decide if it is created or createdby):
-- Line 289
	(
	SELECT Top 1 Created  
	FROM 
	(	
	SELECT TOP 2 PatientCorrespondence.Createdby
	FROM
        PatientVisit pv1 
		INNER JOIN PatientCorrespondence ON pv1.PatientVisitId = PatientCorrespondence.PatientVisitId AND PatientCorrespondence.Description <> 'Visit Transferred to Collection' AND PatientCorrespondence.Description <> 'Visit Removed from Collection' 
	WHERE
	pv1.PatientVisitId = PatientVisit.PatientVisitId
	ORDER BY 
	PatientCorrespondence.Created DESC
	) pc2
	ORDER BY 
	  pc2.Created
	) AS CorrNotes2,

Open in new window

0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question