Torna al Thread
use tempdb;
GO
CREATE TABLE #Table1
(
[Id] int NOT NULL PRIMARY KEY,
[DaOr] datetime NOT NULL
) ON [PRIMARY]
INSERT #Table1 ([Id], [DaOr]) VALUES
(1, N'2015-02-02T13:05:00'),
(2, N'2015-02-02T11:10:00'),
(3, N'2015-02-02T12:00:00'),
(9, N'2015-12-10T15:32:00'),
(10, N'2015-12-10T15:00:00'),
(11, N'2015-12-10T16:52:00'),
(12, N'2015-12-12T23:00:30'),
(13, N'2015-06-04T22:00:00'),
(14, N'2015-02-02T10:40:40'),
(15, N'2015-01-02T14:00:08'),
(16, N'2015-02-02T10:00:00');
GO
SELECT
id
, HourDate = CAST(CONVERT(char(17), DaOr, 113) as datetime)
, progressivo = ROW_NUMBER() OVER(ORDER BY DaOr)
INTO #TempHourDates
FROM
#Table1;
WITH CTE AS
(
SELECT
T1.Id
, KeepOrDelete = CASE WHEN DATEDIFF(MINUTE, T1.HourDate, T2.HourDate) > 60 THEN 'KEEP' WHEN T2.HourDate IS NULL THEN 'KEEP' ELSE 'DEL' END
FROM
#TempHourDates T1
LEFT JOIN #TempHourDates T2 ON T1.progressivo + 1 = T2.progressivo
)
DELETE FROM #Table1
WHERE Id IN
(
SELECT Id FROM CTE WHERE CTE.KeepOrDelete = 'DEL'
);
SELECT * FROM #Table1;
DROP TABLE #Table1;
GO
DROP TABLE #TempHourDates;
GO