SQL Server – How to Check If Logs Have 7 Consecutive Days

gaps-and-islandssql serversql-server-2008

Objective

Below is the example data format. I need to get the in day 1, 2, 3, and so on.. On each account.

+------------+----------------+----------------+---------------+--------------+-----+
| m_idPlayer |   Start_Time   |    End_Time    | TotalPlayTime |   account    | SEQ |
+------------+----------------+----------------+---------------+--------------+-----+
|    0000001 | 20170202131759 | 20170202132311 |           312 | developer    |   1 |
|    0000001 | 20170202132351 | 20170202132615 |           144 | developer    |   2 |
|    0000001 | 20170202135834 | 20170202141342 |           908 | developer    |   3 |
|    0000002 | 20170202141645 | 20170202141814 |            89 | runewars21   |   4 |
|    0000003 | 20170202142341 | 20170202142559 |           138 | speedyturtle |   5 |
|    0000004 | 20170202142700 | 20170202142756 |            56 | kevlaire05   |   6 |
|    0000005 | 20170202142915 | 20170202143225 |           190 | infinity13   |   8 |
|    0000003 | 20170202142609 | 20170202143225 |           376 | speedyturtle |   9 |
|    0000002 | 20170202141820 | 20170202143721 |          1141 | runewars21   |  10 |
|    0000001 | 20170202142821 | 20170202143742 |           561 | developer    |  11 |
+------------+----------------+----------------+---------------+--------------+-----+
  • I need to check on that record if the user has logged in and played with a specific total play time (example: Day 1 – required to play for 2 hours)
  • Also check if the user has logged in for the consecutive days.

Example expected results where account is developer

+-----------+---------+-----------+------------+-----------+
| account   | player  | Day 1     | Day 2      | And so on |
+-----------+---------+-----------+------------+-----------+
| developer | 0000001 | 1925 secs | null       |           |
+-----------+---------+-----------+------------+-----------+

Best Answer

IMHO your question requires a PIVOT solution.

CTE statment get the sum of seconds grouped by developer and day.

SELECT   account, 
         m_idPlayer AS player, 
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec, 
         DATEPART(DAY, Start_Time) as Dy
from     logs
GROUP BY account, m_idPlayer, DATEPART(DAY, Start_Time)
GO

account      | player  |  Sec | Dy
:----------- | :------ | ---: | -:
developer    | 0000001 | 1984 |  2
infinity13   | 0000005 |  190 |  2
kevlaire05   | 0000004 |   56 |  2
runewars21   | 0000002 | 1230 |  2
speedyturtle | 0000003 |  514 |  2

PIVOT part, simply shows this data in columns. Keep in mind you must add all days of one month or deal with a dynamic query.

WITH grp AS
(
SELECT   account, 
         m_idPlayer AS player, 
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec, 
         DATEPART(DAY, Start_Time) as Dy
FROM     logs
GROUP BY account, m_idPlayer, DATEPART(DAY, Start_Time)
)
    SELECT account, player, [1], [2], [3], [4]
    FROM
    (SELECT account, player, Sec, Dy
     FROM   grp
    ) src
    PIVOT
    (SUM(Sec) FOR Dy IN ([1], [2], [3], [4])) as Pvt
;

account      | player  |    1 |    2 |    3 |    4
:----------- | :------ | ---: | ---: | ---: | ---:
developer    | 0000001 | null | 1984 | null | null
runewars21   | 0000002 | null | 1230 | null | null
speedyturtle | 0000003 | null |  514 | null | null
kevlaire05   | 0000004 | null |   56 | null | null
infinity13   | 0000005 | null |  190 | null | null

dbfiddle here

In reference to the second (or main) question:

How to check if logs has 7 consecutive days?

I've used a STD solution, you can find it more by googling a bit.

SQL and contiguous data ranges.

create table logs
(
m_idPlayer varchar(20), 
Start_Time datetime, 
End_Time datetime, 
TotalPlayTime int, 
account varchar(20), 
SEQ int);
insert into logs values
('0000001','2017/02/02 13:17.59','2017/02/02 13:23:11',  312,'developer',1),
('0000001','2017/02/03 13:23:51','2017/02/03 13:26:15',  144,'developer',2),
('0000001','2017/02/04 13:58:34','2017/02/04 14:13:42',  908,'developer',3),
('0000001','2017/02/05 14:16:45','2017/02/05 14:18:14',   89,'developer',4),
('0000001','2017/02/06 14:23:41','2017/02/06 14:25:59',  138,'developer',5),
('0000001','2017/02/07 14:27:00','2017/02/07 14:27:56',   56,'developer',6),
('0000001','2017/02/08 14:29:15','2017/02/08 14:32:25',  190,'developer',8),
('0000001','2017/02/10 14:26:09','2017/02/10 14:32:25',  376,'developer',9),
('0000001','2017/02/11 14:18:20','2017/02/11 14:37:21', 1141,'developer',10),
('0000001','2017/02/12 14:28:21','2017/02/12 14:37:42',  561,'developer',11);
GO

10 rows affected

DECLARE @StartDate datetime = '2017/02/02';
DECLARE @EndDate   datetime = '2017/02/12';

WITH gpr AS
(
SELECT   account, 
         m_idPlayer AS player, 
         SUM(DATEDIFF(SECOND, Start_Time, End_Time)) Sec, 
         DATEADD(DAY, 0, DATEDIFF(DAY, 0, [Start_Time])) as Dt
from     logs
GROUP BY account, m_idPlayer, DATEADD(DAY, 0, DATEDIFF(DAY, 0, [Start_Time]))
), grp2 AS
(
    SELECT account,
           player,
           Dt,
           DATEDIFF(DAY, '17530101', Dt) - ROW_NUMBER() OVER (PARTITION BY account ORDER BY Dt) AS GroupID
    FROM gpr
    WHERE Dt BETWEEN @StartDate AND @EndDate
)
SELECT     grp2.account,
         grp2.player,
         MIN(grp2.Dt )AS StartDate,
         MAX(grp2.Dt) AS Enddate
FROM     grp2
GROUP BY grp2.account,
         grp2.player
HAVING    COUNT(*) >= 7;

GO

account   | player  | StartDate           | Enddate            
:-------- | :------ | :------------------ | :------------------
developer | 0000001 | 02/02/2017 00:00:00 | 12/02/2017 00:00:00

dbfiddle here

Related Question