Z
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
