asked on
SQL Help with Sub Query

First and foremost, the SQL is handled dynamically by the SQL server, therefore please disregard the look to this coding, mostly found in my WHERE clauses. IT looks funny to you, but trust me its not the issue. I need help joining in my tables or with this subquery. Right now, If I leave off the joins in my WHERE clause (AND pv1.PatientVisitId = PatientVisit.PatientVisitId) and (AND pvp1.PatientVisitProcsId = PatientVisitProcs.PatientVisitId) , I get every record with the same Adjustment Type. When I just pick the (AND pv1.PatientVisitId = PatientVisit.PatientVisitId) , every code in the visit gets the same adjustment type. When I add both in I get NULLs. Any help is appreciated!!!!!!

I need help with the the following sub-query.
(
      SELECT TOP 1
            MedLists.Description
      FROM
            PaymentMethod pm
            INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId
            INNER JOIN Transactions tr ON vt.VisitTransactionsId = tr.VisitTransactionsId
            INNER JOIN TransactionDistributions td ON tr.TransactionsId = td.TransactionsId
            INNER JOIN PatientVisit pv1 ON vt.PatientVisitid = pv1.PatientVisitId
            INNER JOIN MedLists ON tr.ActionTypeMId = MedLists.MedListsId
            INNER JOIN PatientVisitProcs pvp1 ON td.PatientVisitProcsId = pvp1.PatientVisitProcsId
      WHERE
            tr.Type = 'A'
            AND pv1.PatientVisitId = PatientVisit.PatientVisitId
            AND pvp1.PatientVisitProcsId = PatientVisitProcs.PatientVisitId
      ) AS AdjustType,
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 sex
	(
	(Null IS NOT NULL AND PatientProfile.sex 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
	(
	(NULL IS NOT NULL AND  PatientProfile.PatientProfileID 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 
	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.sex,'')as PatSex,
	PatientVisit.Entered AS [Date Of Entry], 
	DoctorFacility_1.ListName AS Facility, 
     	MedLists_1.Description AS [Visit Status], 
	PatientVisitProcs.TotalFee AS Fee, 
	PatientVisitProcs.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
        		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, 
	(
	SELECT TOP 1
		MedLists.Description
	FROM	
		PaymentMethod pm
		INNER JOIN VisitTransactions vt ON pm.PaymentMethodId = vt.PaymentMethodId 
		INNER JOIN Transactions tr ON vt.VisitTransactionsId = tr.VisitTransactionsId 
		INNER JOIN TransactionDistributions td ON tr.TransactionsId = td.TransactionsId 
		INNER JOIN PatientVisit pv1 ON vt.PatientVisitid = pv1.PatientVisitId 
		INNER JOIN MedLists ON tr.ActionTypeMId = MedLists.MedListsId 
		INNER JOIN PatientVisitProcs pvp1 ON td.PatientVisitProcsId = pvp1.PatientVisitProcsId 
	WHERE  
		tr.Type = 'A'
		AND pv1.PatientVisitId = PatientVisit.PatientVisitId
		AND pvp1.PatientVisitProcsId = PatientVisitProcs.PatientVisitId  
	) AS AdjustType,	
	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