Menu
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?
787
DATEDIFF() in SQL The DATEDIFF() function compares two dates and returns the difference. The DATEDIFF() function is specifically used to measure the difference between two dates in years, months, weeks, and so on.
The standard gaps-and-island solution is to group by (value minus row_number), since that is invariant within a consecutive sequence. The start and end dates are just the MIN() and MAX() of the group. Very clever solution. Thanks!
To number rows in a result set, you have to use an SQL window function called ROW_NUMBER() . This function assigns a sequential integer number to each result row.
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
120
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
