[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 303
  • Last Modified:

SQL 2005 - Subquery help

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.

With that being said, this is what I need some assistance with. My sub-query is pull a value although it does not seem to be the most recent one. My sub-query can be found in my snippet below - field name CorrNotes. I would like it to pull the most recent entry in this column.

Question 2 - If I wanted to pull the second most recent note into another field, how would I do that? And a third and a fourth most recent....
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.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],
	PatientVisitProcs.Units AS Units,
	MedLists_2.Code AS PlaceOfService, 
	MedLists_3.Code AS TypeOfService, 
	(
	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' AND
			                                PatientCorrespondence.Description NOT LIKE '%Changed Collections Status%' AND
			                                PatientCorrespondence.Description NOT LIKE '%Changed Next Contact Date%'
	WHERE
	pv1.PatientVisitId = PatientVisit.PatientVisitId
	)AS CorrNotes,
	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
Jeff S
Asked:
Jeff S
  • 3
  • 2
2 Solutions
 
David ToddSenior DBACommented:
Hi,

I see a select top 1 ...
with no corresponding order by clause.

That is, if you don't specify an order, then SQL will be lazy and use the most convenient order it can.

HTH
  David
0
 
David ToddSenior DBACommented:
Question 2:

Second most recent row using Northwind as a source

HTH
  David
use Northwind
go

-- latest order
select 
	top 1 
	o.*
from dbo.Orders o
where
	o.CustomerID = 
		(
		select 
			ci.CustomerID
		from dbo.Customers ci
		where
			ci.CompanyName = 'Ernst Handel'
		)
order by
	o.OrderDate desc
;

-- one before latest order
select 
	top 1
	*
from (
	select 
		top 2 
		o.*
	from dbo.Orders o
	where
		o.CustomerID = 
			(
			select 
				ci.CustomerID
			from dbo.Customers ci
			where
				ci.CompanyName = 'Ernst Handel'
			)
	order by
		o.OrderDate desc
	) oo
order by
	oo.OrderDate asc
;

Open in new window

0
 
Jeff SAuthor Commented:
David -
Thank you, Thank you, Thank you for the help on the SQL subquery catch on the order by. I am still relatively new to SQL and trying to learn basically on my own. Thank you for the help. I am studying your post on the second most recent row and trying to get the SQL syntax right.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Jeff SAuthor Commented:
David -
I am chopping the SQL up on the second most recent one ... can you pitch me a hand?
This is the subquery as it is now ....

(
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' AND
PatientCorrespondence.Description NOT LIKE '%Changed Collections Status%' AND
PatientCorrespondence.Description NOT LIKE '%Changed Next Contact Date%'
WHERE
pv1.PatientVisitId = PatientVisit.PatientVisitId
ORDER BY PatientCorrespondence.Created DESC
)AS CorrNotes,
0
 
vinurajrCommented:
select top 1 ...from ..
where Notes not in(
select top 1 from ...
where ....
order by PatientCorrespondence.Created DESC
)

i.e Repeating the same query with NOT IN
0
 
David ToddSenior DBACommented:
Hi Jeff,

I didn't see a question.

Cheers
  David
0

Featured Post

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now