0

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       |           |
+-----------+---------+-----------+------------+-----------+
  • 2
    Can you please tag this question with whichever platform you're using, and add any code you've tried so far? – Erik Darling Mar 29 '17 at 14:35
  • hi @sp_BlitzErik I've added sql-server-2008 for now I'm still searching for the possible solution. – Ron June Lopez Mar 29 '17 at 17:03
  • Can we assume that we'll only ever have 7 days worth of data? Or will 7 days be arbitrary? If day 1 they are required to play for 2 hours, what about day 3? Does the amount of hours played vary based on day (or player id or something else)? You have an fun problem so I'd like to hear more about it – billinkc Mar 29 '17 at 18:15
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.