SQLTeam.com | Weblogs | Forums

Need to get the time slot splitting for every 15 mins

hi All,

Request your help in getting the time slot for each record.

I'll split the day into 96 slots i.e 15 mins each.

i have record time as 2020-04-29 08:31:05.907 which come under the slot 35.

i have a query to split the day into 15 mins

With temp
As
(
SELECT 1 as Time_ID, RIGHT(CONVERT(VARCHAR(16),DATEADD(day, DATEDIFF(day,0,GETDATE()),0),120),15) as Time_Slot
UNION ALL
SELECT Time_ID+1 ,RIGHT(CONVERT(VARCHAR(16),DATEADD(minute, Time_ID15,DATEADD(day, DATEDIFF(day,0,GETDATE()),0) ) ,120),15)
FROM temp
WHERE DATEADD(minute, Time_ID
15,DATEADD(day, DATEDIFF(day,0,GETDATE()),0))
<DATEADD(day,DATEDIFF(day,0,GETDATE())+1,0)
)
select * from temp

now i need to get the particular time slot in a column

hi

hope this helps :slight_smile:

; WITH 
  dates AS 
		(
		  SELECT CAST('2009-01-01' AS datetime) 'date'
			UNION ALL
		  SELECT DATEADD(mi, 15, t.date) 
			FROM dates t
			  WHERE DATEADD(mi, 15, t.date) < '2009-01-02'
				) , 
  rn_dates AS
	(
		SELECT row_number() over(order by cast([date] as time)) time_slot , cast([date] as time) as [date] from dates
	)
  SELECT  min(time_slot) -1 as TimeSlot from rn_dates where [date] >= cast('2020-04-29 08:31:05.907' as time) 

image

image

Try to stay (far) away from recursion for this type of thing, because recursion is much more overhead.

Use a tally table instead, for example:

DECLARE @base_date datetime
SET @base_date = '20200429'
--SET @base_date = 0

;WITH
cte_tally10 AS (
    SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS numbers(number)
),
cte_tally1000 AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS number
    FROM cte_tally10 c1 CROSS JOIN cte_tally10 c2 CROSS JOIN cte_tally10 c3
)
SELECT t.number AS Time_ID, DATEADD(MINUTE, (t.number - 1) * 15, @base_date) AS Time_Slot
FROM cte_tally1000 t
WHERE t.number <= 96
ORDER BY t.number