SQL 2000 Query Assistance

First and foremost, this is for SQL 2000 not SQL 2005. The client is on SQL 2000 until July of this year, so having a coding method that will work in both SQL 2000 and SQL 2005 will be nice.

Currently whats happening is when I run the

SELECT * FROM cusMMElig WHERE PatientProfileId = '241742' and AppDate = '08/04/2007' I get back three rows. I am getting back three rows because the client changed the ApptStatus 3 times. The problem with this code is that its pulling the first apptstatus as the apptstatus, when in fact I would want the most recent one.

This is the Join causing the issue:

LEFT OUTER JOIN dbo.cusCRIMedLists crml4 ON E.EligAppStatMId = crml4.MedListsId

Essentially, I want the most recent row not the first created row.
SET NOCOUNT ON

SELECT PatientName = CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix, '')) <> ''
                           THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix, ''))
                                      + ', ' + ISNULL(pp.First, '') + ' '
                                      + ISNULL(pp.Middle, ''))
                           ELSE RTRIM(ISNULL(pp.First, '') + ' '
                                      + ISNULL(pp.Middle, ''))
                      END,
        pp.PatientProfileId,
        pp.PatientId,
        pp.Address1,
        pp.Address2,
        pp.Suffix,
        pp.City,
        pp.State,
        pp.Zip,
        pp.Phone1,
        pp.Phone1Type,
        pp.Phone2,
        pp.Phone2Type,
        pp.EMailAddress,
        EligName = CASE WHEN RTRIM(E.ELP_Last + ' ' + ISNULL(E.ELP_Suffix, '')) <> ''
                        THEN RTRIM(RTRIM(E.ELP_Last + ' '
                                         + ISNULL(E.ELP_Suffix, '')) + ', '
                                   + ISNULL(E.ELP_First, '') + ' '
                                   + ISNULL(E.ELP_Middle, ''))
                        ELSE RTRIM(ISNULL(E.ELP_First, '') + ' '
                                   + ISNULL(E.ELP_Middle, ''))
                   END,
        E.ELP_Address1,
        E.ELP_Address2,
        E.ELP_City,
        E.ELP_State,
        E.ELP_Zip,
        E.ELP_Phone1,
        E.ELP_Phone1Type,
        E.ELP_Phone2,
        E.ELP_Phone2Type,
        E.ELP_SSN,
        E.ELP_Birthdate,
        E.AppDte,
        E.EligDecisionDte,
        E.EligPerStart,
        E.EligPerEnd,
        E.IncRptLastDte,
        E.IncRptDueDte,
        crml2.Description AS IncRptRcvd,
        E.RILstDte,
        E.RIAnnDueDte,
        crml3.Description AS RIRcvd,
        E.RIClrdDte,
        E.IncRptClrdDte,
        crml4.Description AS AppStatus,
        crml1.Description AS Decision,
        wrkr.Description AS Worker,
        aid.Description AS AidType
FROM    dbo.cusMMElig E
        INNER JOIN dbo.PatientProfile pp ON E.PatientProfileID = pp.PatientProfileId
        LEFT OUTER JOIN dbo.cusCRIMedLists aid ON E.EligTCMSAidTypeMId = aid.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml1 ON E.EligDecisionMId = crml1.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml4 ON E.EligAppStatMId = crml4.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists wrkr ON E.EligAssignedWrkMId = wrkr.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml3 ON E.EligRIRcvdMId = crml3.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml2 ON E.EligIncRptRcvdMId = crml2.MedListsId
WHERE   

	crml1.Description LIKE 'Pend%'
        AND crml4.Description NOT IN ( 'Discontinued' )
        AND ( E.EligDecisionDte IS NULL
              OR E.EligDecisionDte >= ISNULL(NULL, '1/1/1900')
              AND E.EligDecisionDte < DATEADD(d, 1, ISNULL(NULL, '1/1/3000'))
            )

Open in new window

LVL 7
Jeff SAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
so add this subquery...

you may need to extend the correlation data if profileid isn't the correct level of "Key" columns
SET NOCOUNT ON

SELECT PatientName = CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix, '')) <> ''
                           THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix, ''))
                                      + ', ' + ISNULL(pp.First, '') + ' '
                                      + ISNULL(pp.Middle, ''))
                           ELSE RTRIM(ISNULL(pp.First, '') + ' '
                                      + ISNULL(pp.Middle, ''))
                      END,
        pp.PatientProfileId,
        pp.PatientId,
        pp.Address1,
        pp.Address2,
        pp.Suffix,
        pp.City,
        pp.State,
        pp.Zip,
        pp.Phone1,
        pp.Phone1Type,
        pp.Phone2,
        pp.Phone2Type,
        pp.EMailAddress,
        EligName = CASE WHEN RTRIM(E.ELP_Last + ' ' + ISNULL(E.ELP_Suffix, '')) <> ''
                        THEN RTRIM(RTRIM(E.ELP_Last + ' '
                                         + ISNULL(E.ELP_Suffix, '')) + ', '
                                   + ISNULL(E.ELP_First, '') + ' '
                                   + ISNULL(E.ELP_Middle, ''))
                        ELSE RTRIM(ISNULL(E.ELP_First, '') + ' '
                                   + ISNULL(E.ELP_Middle, ''))
                   END,
        E.ELP_Address1,
        E.ELP_Address2,
        E.ELP_City,
        E.ELP_State,
        E.ELP_Zip,
        E.ELP_Phone1,
        E.ELP_Phone1Type,
        E.ELP_Phone2,
        E.ELP_Phone2Type,
        E.ELP_SSN,
        E.ELP_Birthdate,
        E.AppDte,
        E.EligDecisionDte,
        E.EligPerStart,
        E.EligPerEnd,
        E.IncRptLastDte,
        E.IncRptDueDte,
        crml2.Description AS IncRptRcvd,
        E.RILstDte,
        E.RIAnnDueDte,
        crml3.Description AS RIRcvd,
        E.RIClrdDte,
        E.IncRptClrdDte,
        crml4.Description AS AppStatus,
        crml1.Description AS Decision,
        wrkr.Description AS Worker,
        aid.Description AS AidType
FROM    dbo.cusMMElig E
        INNER JOIN dbo.PatientProfile pp ON E.PatientProfileID = pp.PatientProfileId
        LEFT OUTER JOIN dbo.cusCRIMedLists aid ON E.EligTCMSAidTypeMId = aid.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml1 ON E.EligDecisionMId = crml1.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml4 ON E.EligAppStatMId = crml4.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists wrkr ON E.EligAssignedWrkMId = wrkr.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml3 ON E.EligRIRcvdMId = crml3.MedListsId
        LEFT OUTER JOIN dbo.cusCRIMedLists crml2 ON E.EligIncRptRcvdMId = crml2.MedListsId
WHERE   

	crml1.Description LIKE 'Pend%'
        AND crml4.Description NOT IN ( 'Discontinued' )
        AND ( E.EligDecisionDte IS NULL
              OR E.EligDecisionDte >= ISNULL(NULL, '1/1/1900')
              AND E.EligDecisionDte < DATEADD(d, 1, ISNULL(NULL, '1/1/3000'))
            )
            and e.LastModifiedDate =(select max(LastModifiedDate) from dbo.cusMMElig as x
                                     where E.PatientProfileID=x.PatientProfileID)

Open in new window

0
 
LowfatspreadCommented:
so which date (on which table) determines the latest information?
0
 
HainKurtSr. System AnalystCommented:
use this

SELECT PatientProfileId, max(AppDate) MostRecentDate
FROM cusMMElig
group by PatientProfileId

then if you join this with original table

create view v_cusMMElig as
select * from cusMMElig c inner join (
SELECT PatientProfileId, max(AppDate) MostRecentDate
FROM cusMMElig
group by PatientProfileId
) m on c.PatientProfileId=m.PatientProfileId and c.AppDate=m.MostRecentDate

this view will give you most recent records per PatientProfileId. then use this view instead of table on your original post...
0
 
Jeff SAuthor Commented:
LowFatSpread -

Love the name btw. LastModifiedDate on cusMMElig
0
All Courses

From novice to tech pro — start learning today.