Need to get the time slot splitting for every 15 mins

Transact-SQL
1

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_ID15,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

2

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

3

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