Heh... sorry... more trouble in paradise. lfor's code allows dupes for a given time. Mine does not. If dupes must be reported based on duplicate times, then change the ROW_NUMBER() in my code to RANK() and Bob's your uncle.
As a bit of a sidebar, I found this out by using the following code to create a million rows of random data. There are only 96 fifteen minute slots on the clock for any given day. Mine was coming up with only 96 rows no matter how many times I'd run the code. lfor's was with anywhere from 96 to 100 rows (0 to 4 duplicate times).
If you want to play around with it, here's the million row code test
.--===== Drop the temp table if it exists to make reruns in SSMS easier.
DROP TABLE IF EXISTS #Sample
;
--===== Create the same table as a temp table.
CREATE TABLE #Sample
(
[Time] TIME
,Col1 INT
,Col2 INT
,Col3 INT
)
;
--===== Insert a million rows of randomized data.
INSERT INTO #Sample WITH(TABLOCK)
([Time], Col1, Col2, Col3)
SELECT TOP 1000000
[Time] = CONVERT(TIME,CONVERT(DATETIME,RAND(CHECKSUM(NEWID()))*86400))
,Col1 = ABS(CHECKSUM(NEWID())%10)
,Col2 = ABS(CHECKSUM(NEWID())%10)
,Col3 = ABS(CHECKSUM(NEWID())%10)
FROM sys.all_columns ac1 CROSS JOIN sys.all_columns ac2
;
--===== Begin measuring time and I/O
SET STATISTICS TIME,IO ON
;
--===== Jeff's code (this one allows the dupes by using RANK instead of ROW_NUMBER()
WITH cteSegment AS
(
SELECT *
,Segment = RANK()OVER(PARTITION BY DATEDIFF(mi,0,[Time])/15 ORDER BY [Time])
FROM #Sample
)
SELECT [Time], col1, col2, col3
FROM cteSegment
WHERE Segment = 1
ORDER BY [Time]
;
--===== lfor's code. Inherently allows dupes due to the use of MIN()
WITH MinTimes
AS
(
SELECT S.[Time], S.col1, S.col2, S.col3
,MIN(S.[Time]) OVER (PARTITION BY X.Period15) AS MinTime
FROM #sample S
CROSS APPLY
(
VALUES
(
DATEPART(hour, S.[Time])* 4 + DATEPART(minute, S.[Time])/15
)
) X (Period15)
)
SELECT [Time], col1, col2, col3
FROM MinTimes
WHERE [Time] = MinTime
ORDER BY [Time];
--===== Begin measuring time and I/O
SET STATISTICS TIME,IO OFF
;
This also exemplifies why you might want to go for the simpler code. Here's the Time/IO for my code followed by that for lfor's code.
Table '#Sample____________________________________________________________
Scan count 13, logical reads 3216, physical reads 0, read-ahead reads 0, l
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-a
SQL Server Execution Times:
CPU time = 2497 ms, elapsed time = 264 ms.
Table '#Sample____________________________________________________________________________________
Table 'Worktable'. Scan count 36, logical reads 2032843, physical reads 0, read-ahead reads 3472,
SQL Server Execution Times:
CPU time = 4659 ms, elapsed time = 537 ms.
2032843 logical reads = 2032843 /128 = 15,881 MB or almost 15.9 Giga Bytes of I/O.
.
