SQLTeam.com | Weblogs | Forums

Query in 15 mins interval

                           input                            

time col1 col2 col3

10:00:00.0000000 1 3 6
10:14:00.0000000 2 7 6
10:15:00.0000000 2 2 6
10:27:00.0000000 1 3 6
10:28:00.0000000 9 3 2
10:33:00.0000000 3 4 6
10:40:00.0000000 7 5 4
10:44:00.0000000 5 4 1
10:45:00.0000000 9 7 4
11:01:00.0000000 2 9 2
11:16:00.0000000 6 1 6
11:17:00.0000000 6 9 6
11:30:00.0000000 7 3 6

                      output                            

time col1 col2 col3

10:00:00.0000000 1 3 6
10:15:00.0000000 2 2 6
10:33:00.0000000 3 4 6
10:45:00.0000000 9 7 4
11:01:00.0000000 2 9 2
11:16:00.0000000 6 1 6
11:30:00.0000000 7 3 6

I want the output as shown here.please help me in solving the query

10:15:00.0000000 2 2 6
10:33:00.0000000 3 4 6

the above is not 15 minutes, it is 18 minutes. do you have boundaries that allows not exactly 15 minutes?

Yes it's not 15 mins..but I want the data near to that time interval.. after 10:15,10:30 is not there so it has to display the nearest time interval i.e 10:33

please always provide sample data as follows with data type of columns

use sqlteam
go

declare @sample table(_time time, col1 int , col2 int, col3 int)

insert into @sample
select '10:00:00.0000000', 1, 3, 6 union all
select '10:14:00.0000000', 2, 7, 6 union all
select '10:15:00.0000000', 2, 2, 6 union all
select '10:27:00.0000000', 1, 3, 6 union all
select '10:28:00.0000000', 9, 3, 2 union all
select '10:33:00.0000000', 3, 4, 6 union all
select '10:40:00.0000000', 7, 5, 4 union all
select '10:44:00.0000000', 5, 4, 1 union all
select '10:45:00.0000000', 9, 7, 4 union all
select '11:01:00.0000000', 2, 9, 2 union all
select '11:16:00.0000000', 6, 1, 6 union all
select '11:17:00.0000000', 6, 9, 6 union all
select '11:30:00.0000000', 7, 3, 6 

select distinct DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, _time) / 15.0, 0) * 15, 0)
  from @sample
10:27:00.0000000 1 3 6
10:28:00.0000000 9 3 2
10:33:00.0000000 3 4 6

these map to 10.30 but why are you choosing 3,4,6 row? I suspect because it is the closest to the 15 minute mark?

Along with time column, it has to display other columns also...

So it's not possible to get the output as I mentioned above ?????

its possible to get the output as you mentioned

you will have to do a self join ( on the time ) .. and get the other columns

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;
1 Like

Very Nice Ifor

:ok_hand: :ok_hand:

Thank you :slightly_smiling_face::slightly_smiling_face::slightly_smiling_face:

Can you please explain this query..I got the output but I didn't understand the query.

SELECT S._time, S.col1, S.col2, S.col3
	-- The 15 minute period of the day
	,X.Period15
	-- The min time within the 15 minute period.
	,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)
ORDER BY S._time;
1 Like

Okay got it..thank you​:slightly_smiling_face::slightly_smiling_face:

what would be nice is !!!

what was the idea how to do it ? that base level understanding to writing the query
That is the WHOLE .. cream ..

how do you take it from basic idea to the final query ? ..

That knowledge will be useful for ANYONE .. how do you know what to do ? how do you get the idea ?
how to think about it ? how to get it to the final SQL

if you know that PROCESS .. ... sky is the limit

Careful now folks... Ifor's method produces a table spool with 3 scans for 55 logical reads plus the original logical read of 1 for a total of about 437KB of I/O to process 13 rows down to 7.

Table 'Worktable'. Scan count 3, logical reads 55, physical reads 0, read-ahead reads 0
Table '#A8954DAD'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0

The following creates no table spools and 0 logical reads on the "worktable" and 1 logical read for the original data for a total of only 8KB of I/Oand uses simpler code to boot..

   WITH cteSegment AS
(
 SELECT *
        ,Segment = ROW_NUMBER()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
;

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0
Table '#A8954DAD'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0

1 Like

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.
.

1 Like

If you also know the bit of math behind it, there is no limit! :upside_down_face:

:dollar: :dollar: :dollar: :dollar: :dollar: :heavy_dollar_sign: :heavy_dollar_sign: :heavy_dollar_sign: :heavy_dollar_sign: :heavy_dollar_sign: :heavy_dollar_sign:

Thankyou :slightly_smiling_face: