dschuett dschuett - 6 months ago 10
SQL Question

Return Results Of Consecutive Days Per User

I have a table such as the following:

+----+----------------+-------------------------+
| id | employeeNumber | transactionTime |
+----+----------------+-------------------------+
| 1 | 1234 | 2016-02-23 15:11:00.000 |
+----+----------------+-------------------------+
| 2 | 1234 | 2016-02-22 11:01:00.000 |
+----+----------------+-------------------------+
| 3 | 1235 | 2016-02-22 07:22:00.000 |
+----+----------------+-------------------------+
| 4 | 1236 | 2016-02-20 09:16:00.000 |
+----+----------------+-------------------------+
| 5 | 1236 | 2016-02-19 11:01:00.000 |
+----+----------------+-------------------------+
| 6 | 1236 | 2016-02-18 11:44:00.000 |
+----+----------------+-------------------------+
| 7 | 1236 | 2016-02-17 12:12:00.000 |
+----+----------------+-------------------------+
| 8 | 1236 | 2016-02-16 11:09:00.000 |
+----+----------------+-------------------------+
| 9 | 1236 | 2016-02-15 11:19:00.000 |
+----+----------------+-------------------------+
| 10 | 1236 | 2016-02-14 09:12:00.000 |
+----+----------------+-------------------------+


I Need to find a way to return the number of consecutive days that each employee logged a transaction over the past 2 weeks. Such as this:

+------+--------------+-------------------------+-------------------------+
| days |employeeNumber| startTime | endTime |
+------+--------------+-------------------------+-------------------------+
| 2 | 1234 | 2016-02-22 11:01:00.000 | 2016-02-23 15:11:00.000 |
+------+--------------+-------------------------+-------------------------+
| 1 | 1235 | 2016-02-22 11:01:00.000 | 2016-02-22 11:01:00.000 |
+------+--------------+-------------------------+-------------------------+
| 7 | 1236 | 2016-02-14 09:12:00.000 | 2016-02-20 09:16:00.000 |
+------+--------------+-------------------------+-------------------------+


I have been working with the following query, but It only returns a single user and doesn't take into account only the past 2 weeks.

WITH
dates(date) AS (
SELECT DISTINCT CAST(transactionTime AS DATE)
FROM Fuel.dbo.comdata
WHERE employeeNumber = 123456
),
groups AS (
SELECT ROW_NUMBER() OVER (ORDER BY date) AS rn,
DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY date), date) AS grp,
date
FROM dates
)

SELECT COUNT(*) AS consecutiveDates,
MIN(date) AS minDate, MAX(date) AS maxDate
FROM groups
GROUP BY grp
ORDER BY 1 DESC, 2 DESC


Any help is appreciated.

Answer

I would do this with the difference using row number approaches (assuming there is never more than one record per day per employee):

select employee, count(*) as numdays,
       min(timestamp) as startTime, max(timestamp) as endTime
from (select cd.*,
             dateadd(day,
                     - row_number() over (partition by employee order by transactionTime),
                     cast(transactionTime as date)
                    ) as grp
      from Fuel.dbo.comdata cd
     ) cd
group by employee, grp;

The idea is to generate a series of sequential numbers for each employee based on the transactionTime. The difference between this and the transactionTime is constant, when the transactions are on consecutive days.

If you can have multiple transactions on the same day, then you can use dense_rank().

Comments