Yo lo haría así-
primero que nada crear la tabla con los detalles:
Código SQL:
Ver originalCREATE TABLE #T (Hora INT, Minuto INT);
INSERT INTO #T SELECT 12, 1;
INSERT INTO #T SELECT 12, 2;
INSERT INTO #T SELECT 12, 3;
INSERT INTO #T SELECT 12, 4;
INSERT INTO #T SELECT 12, 5;
INSERT INTO #T SELECT 12, 6;
INSERT INTO #T SELECT 12, 17;
INSERT INTO #T SELECT 12, 18;
INSERT INTO #T SELECT 12, 19;
INSERT INTO #T SELECT 12, 20;
INSERT INTO #T SELECT 12, 21;
INSERT INTO #T SELECT 12, 22;
INSERT INTO #T SELECT 12, 33;
INSERT INTO #T SELECT 12, 34;
INSERT INTO #T SELECT 12, 37;
INSERT INTO #T SELECT 13, 20;
INSERT INTO #T SELECT 13, 21;
INSERT INTO #T SELECT 13, 22;
INSERT INTO #T SELECT 13, 23;
INSERT INTO #T SELECT 13, 24;
INSERT INTO #T SELECT 13, 25;
Recuperar los datos en minutos (de lo contrario no podriamos "ver" que después de 12,59 llega 13,00):
Código SQL:
Ver originalSELECT 60*Hora+Minuto AS Minutos,
*
FROM #T;
Con el CTE T1 calculamos la resta entre los minutos y el número de la fila (fijate que cada "grupo" tiene la misma resta):
Código SQL:
Ver originalWITH T1 AS
(SELECT 60*Hora+Minuto AS Minutos,
*
FROM #T)
SELECT Minutos-ROW_NUMBER() OVER(ORDER BY Minutos) Nm,
*
FROM T1;
Con el CTE T2 calculamos el número de filas en cada "grupo":
Código SQL:
Ver originalWITH T1 AS
(SELECT 60*Hora+Minuto AS Minutos,
*
FROM #T),
T2 AS
(SELECT Minutos-ROW_NUMBER() OVER(ORDER BY Minutos) Nm,
*
FROM T1)
SELECT COUNT(*) OVER(Partition BY Nm) Cnt,
*
FROM T2;
Con el CTE T3 filtramos las filas así que queden solamente los "grupos" con 6 filas o mas:
Código SQL:
Ver originalWITH T1 AS
(SELECT 60*Hora+Minuto AS Minutos,
*
FROM #T),
T2 AS
(SELECT Minutos-ROW_NUMBER() OVER(ORDER BY Minutos) Nm,
*
FROM T1),
T3 AS
(SELECT COUNT(*) OVER(Partition BY Nm) Cnt,
*
FROM T2)
SELECT *
FROM T3
WHERE Cnt>=6;
Espero que te sirva de ayuda.