sql server - SQL count consecutive days

Question

This is the SQL database data:

UserTable

UserName    | UserDate      | UserCode
-------------------------------------------
user1       | 08-31-2014    | 232
user1       | 09-01-2014    | 232
user1       | 09-02-2014    | 0
user1       | 09-03-2014    | 121
user1       | 09-08-2014    | 122
user1       | 09-09-2014    | 0
user1       | 09-10-2014    | 144
user1       | 09-11-2014    | 166
user2       | 09-01-2014    | 177
user2       | 09-04-2014    | 188
user2       | 09-05-2014    | 199
user2       | 09-06-2014    | 0
user2       | 09-07-2014    | 155

Should only count consecutive days (as Result) if [UserCode] is something else than zero. UserDate is between 09-01-2014 and 09-11-2014. Show result only if Result is 2 or more.

What I want to my sql query to return is:

UserName    | StartDate     | EndDate       | Result
----------------------------------------------------------
user1       | 09-01-2014    | 09-03-2014    | 2
user1       | 09-08-2014    | 09-11-2014    | 3
user2       | 09-04-2014    | 09-07-2014    | 3

Is this possible using only SQL query?

Answer 1

This is a Gaps and Islands problem. The easiest way to solve this is using ROW_NUMBER() to identify the gaps in the sequence:

SELECT  UserName,
        UserDate,
        UserCode,
        GroupingSet = DATEADD(DAY, 
                            -ROW_NUMBER() OVER(PARTITION BY UserName 
                                                        ORDER BY UserDate), 
                            UserDate)
FROM    UserTable;

This gives:

UserName    | UserDate      | UserCode   | GroupingSet
------------+---------------+------------+-------------
user1       | 09-01-2014    | 1          | 08-31-2014    
user1       | 09-02-2014    | 0          | 08-31-2014    
user1       | 09-03-2014    | 1          | 08-31-2014    
user1       | 09-08-2014    | 1          | 09-04-2014    
user1       | 09-09-2014    | 0          | 09-04-2014    
user1       | 09-10-2014    | 1          | 09-04-2014    
user1       | 09-11-2014    | 1          | 09-04-2014    
user2       | 09-01-2014    | 1          | 08-31-2014    
user2       | 09-04-2014    | 1          | 09-02-2014    
user2       | 09-05-2014    | 1          | 09-02-2014    
user2       | 09-06-2014    | 0          | 09-02-2014    
user2       | 09-07-2014    | 1          | 09-02-2014    

As you can see this gives a constant value in GroupingSet for consecutive rows. You can then group by this colum to get the summary you want:

WITH CTE AS
(   SELECT  UserName,
            UserDate,
            UserCode,
            GroupingSet = DATEADD(DAY, 
                                -ROW_NUMBER() OVER(PARTITION BY UserName 
                                                            ORDER BY UserDate), 
                                UserDate)
    FROM    UserTable
)
SELECT  UserName,
        StartDate = MIN(UserDate),
        EndDate = MAX(UserDate),
        Result = COUNT(NULLIF(UserCode, 0))
FROM    CTE
GROUP BY UserName, GroupingSet
HAVING COUNT(NULLIF(UserCode, 0)) > 1
ORDER BY UserName, StartDate;

Example on SQL Fiddle