• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 220
  • Last Modified:

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.
0
Butterfly2
Asked:
Butterfly2
  • 2
1 Solution
 
PortletPaulCommented:
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
0
 
PortletPaulCommented:
Sorry about the above, but this should return only those with the latest date and flag = 1
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')
      ) AS sq
WHERE modStatus = 1
      AND rn = 1
;

Open in new window

0
 
David ToddSenior DBACommented:
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
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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