SQL 2005 SubQuery help - TOP 1

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)

SQL 2005 SubQuery help - TOP 1

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-05-05 : 10:35:31

I needed to pull the top/most recent record only for these fields below. So I created multiple subquerys with the TOP 1 and Order By DESC in them (please see full query below). I unfortunately still get my records back as if the subquery's never got created. I am stumped. Any ideas?

/* Sliding Fee*/
SFEffectiveDate = cpsfh.SFEffectiveDte,
FamilySize = cpsfh.FamilySize,
MonthlyIncome = Convert(Money,ISNULL(cpsfh.MonthlyIncome,0)),
AnnualIncome = Convert(Money,IsNull(cpsfh.MonthlyIncome*12,0)),
NoIncome = CASE WHEN cpsfh.NoIncome = 0 THEN 'No' ELSE 'Yes' END,
SlidingFeeClass = ISNULL(ccml.description,'None'),
SlidingFeeCarrier = ic2.Listname,
FormOfDeclaration = ISNULL(ccml2.description,'(None)'),
LastModifiedBy = cpsfh.lastmodifiedby,
LastModifiedDate = cpsfh.lastmodified,
/*End Sliding Fee*/

My Query:

/* Patient Insurance List */

SET NOCOUNT ON
SELECT dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS Name,
pp.Address1 AS [Patient Address 1],
pp.Address2 AS [Patient Address 2],
pp.City + ', ' + pp.State + ' ' + pp.Zip AS [Patient Address 3],
g.[Last] + ', ' + g.[First] AS [Guarantor Name],
g.Address1 AS [Guarantor Address 1],
g.Address2 AS [Guarantor Address 2], g.City + ', ' + g.State + ' ' + g.Zip AS [Guarantor Address 3],
pp.PatientSameAsGuarantor,
pi.OrderForClaims AS [Order For Claims],
ISNULL(pi.[Last] + ', ' + pi.[First], '0') AS [Other Name],
pi.Address1 AS [Other Address 1],
pi.Address2 AS [Other Address 2],
pi.City + ', ' + pi.State + ' ' + pi.Zip AS [Other Address 3],
ic.ListName AS [Insurance Carrier],
ISNULL(pi.InsuredId, ' ') AS [Insured ID],
/* Sliding Fee*/
SFEffectiveDate =(SELECT TOP 1 cpsfh.SFEffectiveDte ORDER BY cpsfh.SFEffectiveDte DESC),
FamilySize = (SELECT TOP 1 cpsfh.FamilySize ORDER BY cpsfh.FamilySize DESC),
MonthlyIncome = (SELECT TOP 1 Convert(Money,ISNULL(cpsfh.MonthlyIncome,0))ORDER BY cpsfh.MonthlyIncome DESC),
AnnualIncome = (SELECT TOP 1 Convert(Money,IsNull(cpsfh.MonthlyIncome*12,0))ORDER BY cpsfh.MonthlyIncome*12 DESC),
NoIncome = (SELECT TOP 1 CASE WHEN cpsfh.NoIncome = 0 THEN 'No' ELSE 'Yes' END ORDER BY cpsfh.NoIncome DESC),
SlidingFeeClass = (SELECT TOP 1 ISNULL(ccml.description,'None')ORDER BY ccml.description DESC),
SlidingFeeCarrier = (SELECT TOP 1 ic2.Listname ORDER BY ic2.Listname DESC),
FormOfDeclaration = (SELECT TOP 1 ISNULL(ccml2.description,'(None)')ORDER BY ccml2.description DESC),
LastModifiedBy = (SELECT TOP 1 cpsfh.lastmodifiedby ORDER BY cpsfh.lastmodifiedby DESC),
LastModifiedDate = (SELECT TOP 1 cpsfh.lastmodified ORDER BY cpsfh.lastmodified DESC),
/*End Sliding Fee*/
ISNULL(dbo.formatphone(pp.phone1,1),'') AS Phone,
ISNULL(pp.Phone1Type, ' ') AS [Phone Type],
ig.Name AS InsuranceGroup,
df.ListName AS Doctor,
dff.ListName AS Facility,
ml.Description AS FinancialClass

FROM PatientProfile pp
JOIN PatientInsurance pi ON pp.PatientProfileId = pi.PatientProfileId
JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
LEFT JOIN MedLists ml ON pp.FinancialClassMId = ml.MedListsId
LEFT JOIN DoctorFacility dff ON pp.FacilityId = dff.DoctorFacilityId
LEFT JOIN DoctorFacility df ON pp.DoctorId = df.DoctorFacilityId
LEFT JOIN cusPatientSlidingFeeHst cpsfh ON pp.PatientProfileId = cpsfh.PatientProfileID
LEFT JOIN cusCRIMedLists ccml ON cpsfh.slidingfeeclass = ccml.medlistsid
LEFT JOIN InsuranceCarriers ic2 ON cpsfh.SFCarrierID = ic2.InsuranceCarriersId
LEFT JOIN cusCRIMedLists ccml2 ON cpsfh.SFFormOfDeclarationMID = ccml2.MedListsId

WHERE --Filter on doctor
(
(NULL IS NOT NULL AND pp.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pp.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Insurance Carrier
(
(NULL IS NOT NULL AND pi.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Insurance Group
(
(NULL IS NOT NULL AND ic.InsuranceGroupId IN (NULL)) OR
(NULL IS NULL)
)

ORDER BY dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 10:38:54

TOP 1 from which table?

E 12°55'05.25"
N 56°04'39.16"

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-05-05 : 10:48:31

Ahhhh ....

I changed to this, and got the same results.

/* Sliding Fee*/
SFEffectiveDate =(SELECT TOP 1 cpsfh.SFEffectiveDte FROM cusPatientSlidingFeeHst cpsfh ORDER BY cpsfh.SFEffectiveDte DESC),
FamilySize = (SELECT TOP 1 cpsfh.FamilySize FROM cusPatientSlidingFeeHst cpsfh ORDER BY cpsfh.FamilySize DESC),
MonthlyIncome = (SELECT TOP 1 Convert(Money,ISNULL(cpsfh.MonthlyIncome,0)) FROM cusPatientSlidingFeeHst cpsfh ORDER BY cpsfh.MonthlyIncome DESC),
AnnualIncome = (SELECT TOP 1 Convert(Money,IsNull(cpsfh.MonthlyIncome*12,0)) FROM cusPatientSlidingFeeHst cpsfh ORDER BY cpsfh.MonthlyIncome*12 DESC),
NoIncome = (SELECT TOP 1 CASE WHEN cpsfh.NoIncome = 0 THEN 'No' ELSE 'Yes' END FROM cusPatientSlidingFeeHst cpsfh ORDER BY cpsfh.NoIncome DESC),
SlidingFeeClass = (SELECT TOP 1 ISNULL(ccml.description,'None')FROM cusCRIMedLists ccml ORDER BY ccml.description DESC),
SlidingFeeCarrier = (SELECT TOP 1 ic2.Listname FROM InsuranceCarriers ic2 ORDER BY ic2.Listname DESC),
FormOfDeclaration = (SELECT TOP 1 ISNULL(ccml2.description,'(None)')FROM cusCRIMedLists ccml2 ORDER BY ccml2.description DESC),
LastModifiedBy = (SELECT TOP 1 cpsfh.lastmodifiedby FROM cusPatientSlidingFeeHst cpsfh ORDER BY cpsfh.lastmodifiedby DESC),
LastModifiedDate = (SELECT TOP 1 cpsfh.lastmodified FROM cusPatientSlidingFeeHst cpsfh ORDER BY cpsfh.lastmodified DESC),
/*End Sliding Fee*/

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-05-05 : 10:51:36

I anticipate I have to complete the subquery(s) with a WHERE now ... ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 10:59:17

Yes. You are correct.

E 12°55'05.25"
N 56°04'39.16"

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-05-05 : 11:02:18

My full Query is listed below, yet my results are the same. I am not getting the last record -> I am getting every record (which is not the desired result).

MY FULL QUERY:

--- 292 rows
/* Patient Insurance List */

SET NOCOUNT ON
SELECT dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix) AS Name,
pp.Address1 AS [Patient Address 1],
pp.Address2 AS [Patient Address 2],
pp.City + ', ' + pp.State + ' ' + pp.Zip AS [Patient Address 3],
g.[Last] + ', ' + g.[First] AS [Guarantor Name],
g.Address1 AS [Guarantor Address 1],
g.Address2 AS [Guarantor Address 2], g.City + ', ' + g.State + ' ' + g.Zip AS [Guarantor Address 3],
pp.PatientSameAsGuarantor,
pi.OrderForClaims AS [Order For Claims],
ISNULL(pi.[Last] + ', ' + pi.[First], '0') AS [Other Name],
pi.Address1 AS [Other Address 1],
pi.Address2 AS [Other Address 2],
pi.City + ', ' + pi.State + ' ' + pi.Zip AS [Other Address 3],
ic.ListName AS [Insurance Carrier],
ISNULL(pi.InsuredId, ' ') AS [Insured ID],
/* Sliding Fee*/
SFEffectiveDate =(SELECT TOP 1 cpsfh.SFEffectiveDte FROM cusPatientSlidingFeeHst cpsfh WHERE pp.PatientProfileId = cpsfh.PatientProfileID ORDER BY cpsfh.SFEffectiveDte DESC),
FamilySize = (SELECT TOP 1 cpsfh.FamilySize FROM cusPatientSlidingFeeHst cpsfh WHERE pp.PatientProfileId = cpsfh.PatientProfileID ORDER BY cpsfh.FamilySize DESC),
MonthlyIncome = (SELECT TOP 1 Convert(Money,ISNULL(cpsfh.MonthlyIncome,0)) FROM cusPatientSlidingFeeHst cpsfh WHERE pp.PatientProfileId = cpsfh.PatientProfileID ORDER BY cpsfh.MonthlyIncome DESC),
AnnualIncome = (SELECT TOP 1 Convert(Money,IsNull(cpsfh.MonthlyIncome*12,0)) FROM cusPatientSlidingFeeHst cpsfh WHERE pp.PatientProfileId = cpsfh.PatientProfileID ORDER BY cpsfh.MonthlyIncome*12 DESC),
NoIncome = (SELECT TOP 1 CASE WHEN cpsfh.NoIncome = 0 THEN 'No' ELSE 'Yes' END FROM cusPatientSlidingFeeHst cpsfh WHERE pp.PatientProfileId = cpsfh.PatientProfileID ORDER BY cpsfh.NoIncome DESC),
SlidingFeeClass = (SELECT TOP 1 ISNULL(ccml.description,'None')FROM cusCRIMedLists ccml WHERE cpsfh.slidingfeeclass = ccml.medlistsid ORDER BY ccml.description DESC),
SlidingFeeCarrier = (SELECT TOP 1 ic2.Listname FROM InsuranceCarriers ic2 WHERE cpsfh.SFCarrierID = ic2.InsuranceCarriersId ORDER BY ic2.Listname DESC),
FormOfDeclaration = (SELECT TOP 1 ISNULL(ccml2.description,'(None)')FROM cusCRIMedLists ccml2 WHERE cpsfh.SFFormOfDeclarationMID = ccml2.MedListsId ORDER BY ccml2.description DESC),
LastModifiedBy = (SELECT TOP 1 cpsfh.lastmodifiedby FROM cusPatientSlidingFeeHst cpsfh WHERE pp.PatientProfileId = cpsfh.PatientProfileID ORDER BY cpsfh.lastmodifiedby DESC),
LastModifiedDate = (SELECT TOP 1 cpsfh.lastmodified FROM cusPatientSlidingFeeHst cpsfh WHERE pp.PatientProfileId = cpsfh.PatientProfileID ORDER BY cpsfh.lastmodified DESC),
/*End Sliding Fee*/
ISNULL(dbo.formatphone(pp.phone1,1),'') AS Phone,
ISNULL(pp.Phone1Type, ' ') AS [Phone Type],
ig.Name AS InsuranceGroup,
df.ListName AS Doctor,
dff.ListName AS Facility,
ml.Description AS FinancialClass

FROM PatientProfile pp
JOIN PatientInsurance pi ON pp.PatientProfileId = pi.PatientProfileId
JOIN Guarantor g ON pp.GuarantorId = g.GuarantorId
JOIN InsuranceCarriers ic ON pi.InsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
LEFT JOIN MedLists ml ON pp.FinancialClassMId = ml.MedListsId
LEFT JOIN DoctorFacility dff ON pp.FacilityId = dff.DoctorFacilityId
LEFT JOIN DoctorFacility df ON pp.DoctorId = df.DoctorFacilityId
LEFT JOIN cusPatientSlidingFeeHst cpsfh ON pp.PatientProfileId = cpsfh.PatientProfileID
LEFT JOIN cusCRIMedLists ccml ON cpsfh.slidingfeeclass = ccml.medlistsid
LEFT JOIN InsuranceCarriers ic2 ON cpsfh.SFCarrierID = ic2.InsuranceCarriersId
LEFT JOIN cusCRIMedLists ccml2 ON cpsfh.SFFormOfDeclarationMID = ccml2.MedListsId

WHERE --Filter on doctor
(
(NULL IS NOT NULL AND pp.DoctorId IN (NULL)) OR
(NULL IS NULL)
)
AND
(
(NULL IS NOT NULL AND pp.FacilityId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Insurance Carrier
(
(NULL IS NOT NULL AND pi.InsuranceCarriersId IN (NULL)) OR
(NULL IS NULL)
)
AND --Filter on Insurance Group
(
(NULL IS NOT NULL AND ic.InsuranceGroupId IN (NULL)) OR
(NULL IS NULL)
)

ORDER BY dbo.FormatName(pp.Prefix, pp.First, pp.Middle, pp.Last, pp.Suffix)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-05 : 11:09:35

Drop this line

LEFT JOIN cusPatientSlidingFeeHst cpsfh ON pp.PatientProfileId = cpsfh.PatientProfileID

E 12°55'05.25"
N 56°04'39.16"

JeffS23
Posting Yak Master

212 Posts

Posted - 2008-05-05 : 11:14:13

Dropping this returns this:

Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "cpsfh.slidingfeeclass" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "cpsfh.SFCarrierID" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "cpsfh.SFFormOfDeclarationMID" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "cpsfh.slidingfeeclass" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "cpsfh.SFCarrierID" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "cpsfh.SFFormOfDeclarationMID" could not be bound.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-06 : 02:50:38

Very strange, since you do not select anything from this table in the outer query, only in the correlated subqueries.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources