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
Copyright © dotNetHell.it 2002-2025
Running on Windows Server 2008 R2 Standard, SQL Server 2012 & ASP.NET 3.5