Query to produce time pattern with 5 minutes interval


–Use CTE to in a recursive way to generate the list

;With mycte

As

(

–Define 1 as start as Time_ID and retrieve the part in the format we want with datediff and dateadd function

SELECT 1 as Time_ID, RIGHT(CONVERT(VARCHAR(16),DATEADD(day, DATEDIFF(day,0,GETDATE()),0),120),5) as Time_Slot

UNION ALL

SELECT Time_ID+1 ,RIGHT(CONVERT(VARCHAR(16),DATEADD(minute, Time_ID*5,DATEADD(day, DATEDIFF(day,0,GETDATE()),0) ) ,120),5)

FROM mycte

WHERE DATEADD(minute, Time_ID*5,DATEADD(day, DATEDIFF(day,0,GETDATE()),0))

<DATEADD(day,DATEDIFF(day,0,GETDATE())+1,0)

–You can get more information about recursive CTE from MSDN:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

)

–Create a NewTable with data and structure from the CTE table mycte

SELECT * INTO NewTable FROM mycte

 

–The default recursion is upto 100 level. We explicit the MAXRECURSION number to 300 for our need here

OPTION (MAXRECURSION 300)

 

–Retrieve data from the Newtable

SELECT * FROM NewTable

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s