How to query the maximum number of consecutive logon days

Recently I have encountered many posts about how to solve the problem of consecutive login days. This type of problem is actually a variant of the island problem we often encounter to solve this type of problem. We have the most basic idea: step 1: Find out the point after the interruption, assign them row numbers (this is the starting point of the island) step 2: Find the point before the interruption, assign them a row number (this is the end point of the island) step 3: Use the same row number as the condition, after achieving the above three steps, we can basically solve these problems. Here are three ways to demonstrate: /* name logindatea1 2011-1-2a1 2011-1-3a1 2011-1-4a1 2011-1-7a1 2011-1-12a1 2011-1-13a1 2011-1-16a2 2011-1-7a2 2011-1-8a2 2011-1-10a2 2011-1-11a2 2011-1-13a2 2011-1-24 then the result I need is: name start_d Ay end_day logindaysa1 2011-1-1-4 3a2 2011-1-7 2011-1-8 2a2 2011-1-10 2011-1-11 2 */--> test data: [tbl] if object_id ('[tbl]') is not null drop table [tbl] create table [tbl] ([name] varchar (2), [logindate] date) insert [tbl] select 'a1 ', '2014-1-2 'union allselect 'a1', '2014-1-3' union allselect 'a1', '2014-1-4 'union allselect 'a1 ', '2014-1-7 'union allselect 'a1', '2014-1-12' union allselect 'a1', '2014-1 -13 'Union allselect 'a1', '2017-1-16 'union allselect 'a2', '2017-1-7 'union allselect 'a2 ', '2014-1-8 'union allselect 'a2', '2014-1-10 'union allselect 'a2', '2014-1-11 'union allselect 'a2 ', '2014-1-13 'union allselect 'a2', '2014-1-24 '-- Method 1; with t as (select [name], [logindate], (select min (B. [logindate]) from tbl B where B. [logindate]> =. [logindate] and B. name =. nameand not exists (select * from tbl c Where c. [logindate] = dateadd (dd, 1, B. [logindate]) and c. name = B. name) as grpfrom tbl a), mas (select [name], min ([logindate]) as start_day, max (grp) as end_dayfrom t group by grp, name) select *, (datediff (dd, start_day, end_day) + 1) as logindays from m a where (datediff (dd, start_day, end_day) + 1) in (select max (datediff (dd, start_day, end_day) + 1) from m bwhere. name = B. name )--------------------------------------- Revoke method 2 declare @ date datetimeselect @ date = min (logindate) from tbl; with ach as (select [name], logindate, id = row_number () over (partition by [name] order by logindate) from tbl), t as (select [name], min (logindate) mindate, max (logindate) maxdate, (datediff (dd, min (logindate), max (logindate) + 1) ddda Te from achgroup by [name], datediff (dd, @ date, logindate)-id -- order by [name], mindate) select * from t a where dddate in (select max (dddate) from t B wherea. name = B. name) rows --------------------- method 3; with t as (select name, [logindate], dateadd (dd,-row_number () over (partition by name Order by [logindate]), [logindate]) as diff from tbl), m as (select name, min ([logindate]) as start_day, max ([logindate]) as end_day, (datediff (dd, min ([logindate]), max ([logindate]) + 1) as logindaysfrom tgroup by name, diff) select * from m a where logindays in (select MAX (logindays) from m B where. name = B. name)/* namestart_dayend_daylogindaysa12011-01-022011-01-043a22011-01-102011-01-112a22011-01-072011-0 1-082 */concerning the continuous login problem, that is, the time island problem, our solution is equivalent to the island. In the course of study, I suggest you grasp the most basic method and do not pursue the simplest method. Any simple method is based on a certain degree of basic knowledge and proficiency. Only when you have mastered the most basic things can you find a simpler method. Thank you for reading.