clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 497317 fiddles created (9545 in the last week).

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);
10 rows affected
 hidden batch(es)


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;
account player StartDate Enddate
developer 0000001 02/02/2017 00:00:00 12/02/2017 00:00:00
 hidden batch(es)