Need a simple query to calculate sequence length in SQL Server

Question

I have this view that represent the status of connections for each user to a system inside table as below:

---------------------------------------
|id |   date     | User  |  Connexion |
|1  | 01/01/2018 |  A    |      1     |
|2  | 02/01/2018 |  A    |      0     |
|3  | 03/01/2018 |  A    |      1     |
|4  | 04/01/2018 |  A    |      1     |
|5  | 05/01/2018 |  A    |      0     |
|6  | 06/01/2018 |  A    |      0     |
|7  | 07/01/2018 |  A    |      0     |
|8  | 08/01/2018 |  A    |      1     |
|9  | 09/01/2018 |  A    |      1     |
|10 | 10/01/2018 |  A    |      1     |
|11 | 11/01/2018 |  A    |      1     |
---------------------------------------

The target output would be to get the count of succeeded and failed connection order by date so the output would be like that

---------------------------------------------------------------
|StartDate         EndDate       User     Connexion     Length|
|01/01/2018  |   01/01/2018  |     A    |    1      |      1  |
|02/01/2018  |   02/01/2018  |     A    |    0      |      1  |
|03/01/2018  |   04/01/2018  |     A    |    1      |      2  |
|05/01/2018  |   07/01/2018  |     A    |    0      |      3  |
|08/01/2018  |   11/01/2018  |     A    |    1      |      4  |
---------------------------------------------------------------

And what attempts have you made so far? Could you post the SQL you've tried and tell us why it didn't work please? — Thom A
– Thom A ♦, Commented Nov 13, 2018 at 15:54

Gordon Linoff · Accepted Answer · 2018-11-13 16:08:07Z

4

This is what is called a gaps-and-islands problem. The best solution for your version is a difference of row numbers:

select user, min(date), max(date), connexion, count(*) as length
from (select t.*,
             row_number() over (partition by user order by date) as seqnum,
             row_number() over (partition by user, connexion order by date) as seqnum_uc
      from t
     ) t
group by user, connexion, (seqnum - seqnum_uc);

Why this works is a little tricky to explain. Generally, I find that if you stare at the results of the subquery, you'll see how the difference is constant for the groups that you care about.

Note: You should not use user or date for the names of columns. These are keywords in SQL (of one type or another). If you do use them, you have to clutter up your SQL with escape characters, which just makes the code harder to write, read, and debug.

edited Nov 13, 2018 at 16:08

answered Nov 13, 2018 at 15:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ilyes Over a year ago

[User] and [Date] please

Thom A Over a year ago

Definitely [User], as USER is a reserved word. date is only a keyword, so it can be used for object names unquoted, however, I agree that it should be [quoted].

Collectives™ on Stack Overflow

Need a simple query to calculate sequence length in SQL Server

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related