Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SubQuery - Is this possible?

Author  Topic 

JeffS23
Posting Yak Master

212 Posts
Posted - 2009-12-17 : 00:12:58
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.

I have 3 subquerys in this coding: "CorrNotes", "CorrDate" and "CorrCreatedby".

These 3 subquerys pull the last entrys. What I would love to code is the second most recent row and then a third most recent row for each of these. Is this possible somehow?

[CODE]
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,
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
[/CODE]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts
Posted - 2009-12-17 : 12:13:41
you could use the rank function along with your LastFiledDate field
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )


<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site