How to determine if a person is registered for specific dates in SQL

Question

I have the following table:

+----------+------------+------------+
| MemberID | StartDate  |  EndDate   |
+----------+------------+------------+
|    10    | 2015-01-01 | 9999-12-31 |
|    10    | 2015-06-15 | 9999-12-31 |
|    20    | 2015-01-01 | 2015-04-06 |
|    20    | 2015-04-07 | 9999-12-31 |
|    30    | 2015-05-06 | 9999-12-31 |
|    40    | 2015-01-01 | 2015-03-01 |
|    50    | 2015-01-01 | 2015-08-31 |
+----------+------------+------------+

Based on this table, I want to find Members that CONTINUOUSly apply from January 1, 2015 - August 31, 2015.

Business Rules:

• Even if a member has a one-day gap in his membership, he is disqualified.
• The same member identifier may have different entries in the table (example MemberID 10)
• In the demo table, members 10, 20, 50 should be included in the results table, as they are eligible for the entire period.

Answer 1

The approach is here:

1. , .. EndDate 2015-04-06 StartDate 2015-04-07, . :

SELECT 
        MemberID, MIN(StartDate) StartDate, MAX(EndDate) EndDate
    FROM
        (SELECT 
        dt.*,
            IF(@previd = dt.memberid
                && DATE_ADD(@prevdate, INTERVAL 1 DAY) <= dt.StartDate, @groupid, @groupid:=@groupid + 1) GroupId,
            @previd:=dt.memberid,
            @prevdate:=dt.EndDate
    FROM
        data_table dt, (SELECT @previd:=NULL, @prevdate:=NULL, @groupid:=0) a
    ORDER BY MemberID , StartDate) memberdategrouptable
    GROUP BY GroupId

, MemberID, StartDate if(found_different_member || (member_same_as_previous_row && previous_end_date_does_not_match_to_current_start_date) assign_new_group_id.

:

    +----------+------------+------------+---------+----------------------+-----------------------+
    | MemberID | StartDate  | EndDate    | GroupId | @previd:=dt.memberid | @prevdate:=dt.EndDate |
    +----------+------------+------------+---------+----------------------+-----------------------+
    |       10 | 2015-01-01 | 9999-12-31 |       1 |                   10 | 9999-12-31            |
    |       10 | 2015-06-15 | 9999-12-31 |       2 |                   10 | 9999-12-31            |
    |       20 | 2015-01-01 | 2015-04-06 |       3 |                   20 | 2015-04-06            |
    |       20 | 2015-04-07 | 9999-12-31 |       3 |                   20 | 9999-12-31            |
    |       30 | 2015-05-06 | 9999-12-31 |       4 |                   30 | 9999-12-31            |
    |       40 | 2015-01-01 | 2015-03-01 |       5 |                   40 | 2015-03-01            |
    |       50 | 2015-01-01 | 2015-08-31 |       6 |                   50 | 2015-08-31            |
    +----------+------------+------------+---------+----------------------+-----------------------+

+----------+------------+------------+
| MemberID | StartDate  | EndDate    |
+----------+------------+------------+
|       10 | 2015-01-01 | 9999-12-31 |
|       10 | 2015-06-15 | 9999-12-31 |
|       20 | 2015-01-01 | 9999-12-31 |
|       30 | 2015-05-06 | 9999-12-31 |
|       40 | 2015-01-01 | 2015-03-01 |
|       50 | 2015-01-01 | 2015-08-31 |
+----------+------------+------------+

2. ,

SELECT 
    *
FROM
    (SELECT 
        MemberID, MIN(StartDate) StartDate, MAX(EndDate) EndDate
    FROM
        (SELECT 
        dt.*,
            IF(@previd = dt.memberid
                && DATE_ADD(@prevdate, INTERVAL 1 DAY) <= dt.StartDate, @groupid, @groupid:=@groupid + 1) GroupId,
            @previd:=dt.memberid,
            @prevdate:=dt.EndDate
    FROM
        data_table dt, (SELECT @previd:=NULL, @prevdate:=NULL, @groupid:=0) a
    ORDER BY MemberID , StartDate) memberdategrouptable
    GROUP BY GroupId) memberdaterange 
WHERE
    StartDate <= '2015-01-01'
        AND EndDate >= '2015-08-31'

Reuslt

+----------+------------+------------+
| MemberID | StartDate  | EndDate    |
+----------+------------+------------+
|       10 | 2015-01-01 | 9999-12-31 |
|       20 | 2015-01-01 | 9999-12-31 |
|       50 | 2015-01-01 | 2015-08-31 |
+----------+------------+------------+

Answer 2

, MySQL. , . - . , .

, , , StartDate EndDate ( , ):

select t.*,
       (@grp := if(@m = MemberId and @e = date_sub(StartDate, interval 1 day), @grp,
                   if(@m := MemberId, if(@e := EndDate, @grp + 1, @grp + 1), @grp + 1
                     )
                   )
       ) as grp
from (select t.*,
             (select 1
              from table t2
              where t2.MemberId = t.MemberId and
                    t2.EndDate = date_sub(t.StartDate, interval 1 day)
             ) as IsPeriodStart
      from table t
     ) t cross join
     (select @m := 0, @e := 0, @grp := 0) params
order by MemberId, StartDate;

- having:

select MemberId
from (select t.*,
             (@grp := if(@m = MemberId and @e = date_sub(StartDate, interval 1 day), @grp,
                         if(@m := MemberId, if(@e := EndDate, @grp + 1, @grp + 1), @grp + 1
                           )
                         )
             ) as grp
      from (select t.*,
                   (select 1
                    from table t2
                    where t2.MemberId = t.MemberId and
                          t2.EndDate = date_sub(t.StartDate, interval 1 day)
                   ) as IsPeriodStart
            from table t
           ) t cross join
           (select @m := 0, @e := 0, @grp := 0) params
      order by MemberId, StartDate
     ) t
group by MemberId, grp
having min(StartDate) <= '2015-01-01' and max(EndDate) >- '2015-08-31';

EDIT:

, . . - , IsPeriodStart :

select MemberId
from (select t.*,
             (select 1
              from table t2
              where t2.MemberId = t.MemberId and
                    t2.EndDate = date_sub(t.StartDate, interval 1 day)
             ) as IsPeriodStart
      from table t
      where EndDate >= '2015-01-01' and StartDate <= '2015-08-31'
     ) t 
group by MemberId
having max(case when IsPeriodStart then StartDate end) = min(StartDate) and
       min(StartDate) <= '2015-01-01' and
       max(EndDate) >= '2015-08-31';

having . - . , ; , .

Answer 3

: Member, Eligible.

• () true
• false -

.

 update Member set Eligible = false
 where Member.MemberId =  
   (select A.MemberId from tbl A
    left outer join tbl B on A.MemberId = B.MemberId
    where A.EndDate <> '9999-12-31'
    and A.EndDate +1 < B.StartDate)

mysql, .