Split time with 5 mins interval in sql server

Question

0.00/5 (No votes)

See more:

Hi...
I have the data like below

C#

Id     Time       Location
1     08:00     Test Location
2     08:15     Test Location
3     08:30     Test Location
4     08:45     Test Location
5     09:00     Test Location

and I would like to get the result as shown below

C#

Id     Time       Location
1     08:00     Test Location
1     08:05     Test Location
1     08:10     Test Location
2     08:15     Test Location
2     08:20     Test Location
2     08:25     Test Location
3     08:30     Test Location
3     08:35     Test Location
----------------------------

Please help to get this result

Thanks
Pramod

What I have tried:

I tried with date diff but am not able to get the result...

Posted 26-Feb-16 3:43am

Pramod Bura

Updated 29-Feb-16 4:35am

Add a Solution

Comments

Michael_Davies 26-Feb-16 9:51am

Show us the SQL you used for the Datediff command so we can see what you have tried.

Richard Deeming 26-Feb-16 10:12am

Which version of SQL Server?

Is the source data always in 15 minute intervals, with no breaks?

ZurdoDev 26-Feb-16 12:33pm

One way, and it is sort of brute force, is to have a temp table that has all times in it and then you can union to it to get the missing pieces.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2016-02-28T08:37:00

I really dislike using procedural loops in SQL Server which is why I'm offering this alternative solution.

You can get the results you want by using a recursive CTE.

I created some test data with

SQL

CREATE TABLE TestLocation 
(Id INT IDENTITY(1,1),[Time] NVARCHAR(50),Location NVARCHAR(50))
 
INSERT INTO TestLocation VALUES
('08:00','Test Location'),
('08:15','Test Location'),
('08:30','Test Location1'),
('08:45','Test Location1'),
('09:00','Test Location2')

It's similar to the data you presented in your question but I wanted to change the location values when testing my query.

This single query then gives you the results you wanted

SQL

;with CTE AS
(
	SELECT  cast([Time] AS Time) AS [Time], 1 as RLevel, Id, Location
	FROM TestLocation

	UNION ALL
	SELECT CAST(dateadd(Minute, 5, [Time]) AS Time), RLevel + 1, Id, Location
	FROM CTE
	WHERE RLevel < 3 -- i.e. 15 minutes divided by the 5 added
)
select Id, [Time], Location
from CTE
WHERE [Time] <=  (SELECT MAX([Time]) FROM TestLocation)
order by [Time]

For every record in your original data this rCTE is creating more rows, the first adds 5 minutes onto the time and because this is recursive, the next row adds 5 minutes onto the new row.

Note this only works if the original data is in 15 minute intervals without any breaks.

The easiest way to list all of the times between 08:00 to 09:00 (inclusive) in 5 minute intervals is

SQL

;with CTE AS
(
	SELECT  CAST('08:00' AS Time) AS datum
	UNION ALL
	SELECT dateadd(Minute, 5, datum)
	FROM CTE
	WHERE dateadd(Minute, 5, datum) < dateadd(Minute, 65, CAST('08:00' AS Time))
)
select * from CTE

RDBurmon · Answer 2 · 2016-02-26T16:56:00

I have considered below table for this example

SQL

CREATE TABLE tblTestLocation (Id INT,[Time] NVARCHAR(50),Location NVARCHAR(50))

INSERT INTO tblTestLocation 
SELECT 1,'08:00','Test Location' UNION
SELECT 2,'08:15','Test Location' UNION
SELECT 3,'08:30','Test Location' UNION
SELECT 4,'08:45','Test Location' UNION
SELECT 5,'09:00','Test Location'

Below logic will give you required result

SQL

DECLARE @tmpTestLocation AS TABLE(Id INT,[Time] NVARCHAR (50) ,Location NVARCHAR(50))
DECLARE @FinaltmpTestLocation AS TABLE(Id INT,[Time] NVARCHAR (50) ,Location NVARCHAR(50))

DECLARE @ttlRows AS INT
DECLARE @rowindex as INT
DECLARE @CurrentTime AS DateTime
DECLARE @Id AS Int
DECLARE @Location AS NVARCHAR(50)
DECLARE @NextTime AS DateTime

SELECT @ttlRows =count(Id) FROM tblTestLocation
SET @rowindex =1

WHILE @rowindex  <= @ttlRows
BEGIN
  
  SELECT @Id=Id,@Location=Location,@CurrentTime=CAST([Time] AS DATETIME) FROM tblTestLocation WHERE Id=@rowindex 
  SELECT @NextTime=CAST([Time] AS DATETIME)  FROM tblTestLocation WHERE Id=(@rowindex +1)
    INSERT INTO @tmpTestLocation   
    SELECT Id,[Time],Location FROM tblTestLocation WHERE Id=@rowindex 

	WHILE @CurrentTime < (@NextTime -'00:05:00')
	BEGIN
       SET @CurrentTime =@CurrentTime +'00:05:00'
      
       INSERT INTO @tmpTestLocation   
       SELECT @Id,convert(char(5), @CurrentTime, 108),@Location
              
    End
SET @rowindex  =@rowindex  +1  
END
select  * from @tmpTestLocation

Split time with 5 mins interval in sql server

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0