asked on

filter a flag base on Max date

I am trying to build a query base on a flag - 1 means enrolled and 0 means unenrolled. I need to know all the enrollments with a status = 1 based on the highest modification date.
sample data

SourceId        LastUpdated        modStatus
200103623      8/11/2014 21:35      0
200103623      7/31/2014 23:08      1
200119223      8/3/2014 21:20      1
200119223      7/31/2014 23:08      0

when I do the following query

select distinct SourceId,
MAX( modDt) as LastUpdated,
modStatus
FROM [GSDR].[d2l].[GCPS_Users_Students_delta]as d
where SourceId in ('200103623', '200119223')
and modStatus = 1
group by modStatus,
SourceId

I get the get the following results:

SourceId        LastUpdated        modStatus
200103623      7/31/2014 23:08      1
200119223      8/3/2014 21:20      1

but I just want
SourceId        LastUpdated      modStatus
200119223      8/3/2014 21:20      1

The first record 200103623, is currently unenrolled because the unenrollment date is greater than the enrollment date.

PortletPaul

here are 2 methods:

SELECT
      *
FROM (
            SELECT
                  SourceId
                , modDt
                , modStatus
                , ROW_NUMBER() OVER (PARTITION BY SourceId, ORDER BY modDt DESC) rn
            FROM [GSDR].[d2l].[GCPS_Users_Students_delta] AS d
            WHERE SourceId IN ('200103623', '200119223')
                  AND modStatus = 1
      ) AS sq
WHERE rn = 1
;

SELECT
      *
FROM (
            SELECT
                  SourceId
                , modDt
                , modStatus
                , MAX(modDt) OVER (PARTITION BY SourceId) max_modDt
            FROM [GSDR].[d2l].[GCPS_Users_Students_delta] AS d
            WHERE SourceId IN ('200103623', '200119223')
                  AND modStatus = 1
      ) AS sq
WHERE modDt = max_modDt
;

Open in new window

(+ edit} please ignore I miss read the question - these won't meet your need

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Todd

Hi,

Try something like this:

select 
    d.*
from [GSDR].[d2l].[GCPS_Users_Students_delta] d
inner join (
    select 
        max( di.LastUpdated ) MaxLastUpdated
    from [GSDR].[d2l].[GCPS_Users_Students_delta] di
    where
        di.SourceId IN ('200103623', '200119223')
        AND dimodStatus = 1
    ) dd
    on dd.MaxLastUpdated = d.LastUpdated
where
    d.SourceId IN ('200103623', '200119223')
    AND d.modStatus = 1

Open in new window

HTH
David