Check if value was greater than x for y consecutive months period in changes history

Question

I have table with members level changes. It has historical changes of level of all members and date when change happened. For example I can list changes for member number 5:

select * from memberlevelhistory where member = 5

result:

member  changedate  level
5       2012-04-01  2
5       2012-03-01  3
5       2012-02-01  2
5       2011-02-01  6
5       2011-02-01  6
5       2010-03-15  6
5       2010-02-01  5
5       2010-01-01  5
5       2009-10-01  4
5       2009-08-27  2
5       2009-08-01  1

Last entry in history table is current level.

QUESTION: How to list all members that had level higher or equal to 3 for period of 3 months or more ?

That is a simplified version of the question. To make it even more fun I need only members that didn't drop below the starting level during this 3 month period. So if a member started the 3 month period with level 4 and is only level 3 on the last month then that member is excluded from the list.

Any help, even with the simplified question is much appreciated.

EXTENDED VERSION:

I also need this period of >=3 months of level >=3 happen inside last 6 months window.

Answer 1

This might be a case for not exists. An entry is valid if there is not another entry with level less than currently selected record in next three months. This solves both requirements. There might be an issue with last entry per member where level is appropriate, but span is not known. I've decided to remove those records, but you might have other ideas.

Sql Fiddle with example is here.

select distinct mlh.member
  from memberlevelhistory mlh
 where mlh.level >= 3
   and not exists
       (
         select null
           from memberlevelhistory mlh2
          where mlh2.member = mlh.member
            and mlh2.changedate >= mlh.changedate
            and mlh2.changedate < dateadd(month, 3, mlh.changedate)
            and mlh2.level < mlh.level
       )
    -- The last entry might have appropriate level
    -- But we cannot tell how long it lasted,
    -- So we are going to remove it.
   and exists
       (
         select null
           from memberlevelhistory mlh3
          where mlh3.member = mlh.member
            and mlh3.changedate > mlh.changedate
       )

EDIT:

I have rewritten not exists() to left join and added 'last entry lasting until today' criterion.

Sql Fiddle with example is here.

select distinct mlh.member
  from memberlevelhistory mlh
  left join memberlevelhistory mlh2
    on mlh2.member = mlh.member
   and mlh2.changedate >= mlh.changedate
   and mlh2.changedate < dateadd(month, 3, mlh.changedate)
   and mlh2.level < mlh.level
 where mlh.level >= 3
   and mlh2.member is null
   and datediff(month, mlh.changedate, getdate()) >= 3

Query rewritten:

; with ranges as 
(
  select mlh.member, mlh.changedate StartRange, min(isnull(mlh2.changedate, getdate())) EndDate
    from memberlevelhistory mlh
    left join memberlevelhistory mlh2
      on mlh2.member = mlh.member
     and mlh2.changedate >= mlh.changedate
     and mlh2.level < mlh.level
   where mlh.level >= 3
  group by mlh.member, mlh.changedate
 having datediff (month, min(isnull(mlh2.changedate, getdate())), getdate()) <= 6
    and datediff (month, mlh.changedate, min(isnull(mlh2.changedate, getdate()))) >= 3
)
select distinct member
  from ranges

And Sql Fiddle is here.

I believe that 100 and 101 should be included because both had a good run for 3 months and this was on in march which is six months before this moment.

What I did is to generate ranges when somebody had a good run, and then test this ranges for duration of 3 months or more and ending date in last six months.

UPDATE: if I finally got it right, you need that duration is lasting for three months in last six months. To calculate that one might truncate changedates to current date - six months. Using that as starting point, and finding ending point of a range as first mlh having lower level and higher date as ending point one has enough information to calculate duration.

; with ranges as 
(
  select mlh.member, 
  -- If good range starts more than six months before today
  -- truncate it to today - 6 months
         case when datediff (month, mlh.changedate, getdate()) > 6
              then dateadd(month, -6, getdate())
              else mlh.changedate 
              end StartRange,
  -- First bad mlh after current changedate
         min(isnull(mlh2.changedate, getdate())) EndRange
    from memberlevelhistory mlh
    left join memberlevelhistory mlh2
      on mlh2.member = mlh.member
     and mlh2.changedate >= mlh.changedate
     and mlh2.level < mlh.level
   where mlh.level >= 3
  group by mlh.member, mlh.changedate
  -- As above, limit good range to max six months before today
  -- And only get those lasting at least three months
 having datediff (month, case when datediff(month, mlh.changedate, getdate()) > 6
                              then dateadd(month, -6, getdate())
                              else mlh.changedate 
                          end, 
                         min(isnull(mlh2.changedate, getdate()))) >= 3
)
select distinct member
  from ranges

Sql Fiddle with example is here.