Insert data between 30 minute time intervals

I want to divided my to my time interval depending to the start time and end time. To explain it further, let me have this example.
I have this table:

User           Elapsed Time        Start Time            End Time
User01         01:05               07-02-2015 08:45      07-02-2015 09:50

I want to divide its elapsed time to 30 minute time interval so that i will have an output like this.

Interval From        Interval To         User       Elapsed Time
07-02-2015 08:30     07-02-2015 09:00    User01     00:15
07-02-2015 09:00     07-02-2015 09:30    User01     00:30
07-02-2015 09:30     07-02-2015 10:00    User01     00:20

Thanks in advance

Here's a different approach that doesn't rely on loops or additional tables, however it does touch the source table twice. (You'll have to replace dbo.aTable with the name of your actual table, and add where clauses where commented below if you want to filter.)

DECLARE @i INT = 30; -- interval in minutes

DECLARE @ft SMALLDATETIME, @lt SMALLDATETIME;
SELECT @ft = MIN([Start Time]), @lt = MAX([End Time]) FROM dbo.aTable -- WHERE?;

;WITH d1(dt) AS
(
  SELECT TOP (DATEDIFF(MINUTE,@ft,@lt)/@i+2) 
    DATEADD(MINUTE,DATEDIFF(MINUTE,0,DATEADD(MINUTE,@i*(ROW_NUMBER() 
    OVER (ORDER BY [object_id])-1),@ft))/@i*@i,0)
  FROM sys.all_columns
), d2(s,e) AS (SELECT dt,DATEADD(MINUTE,@i,dt) FROM d1
), t AS
(
  SELECT [User],[Start Time],[End Time],
    StartInterval = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [Start Time])/@i*@i, 0),
    EndInterval   = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, [End Time])/@i*@i, 0)
  FROM dbo.aTable -- WHERE?
),
n AS
(
  SELECT d2.s, d2.e, [User], [Start Time], [End Time],
    sr = CASE WHEN [Start Time] >= d2.s AND [Start Time]  d2.s AND [End Time] <= d2.e THEN 1 END
   FROM d2 INNER JOIN t ON d2.s BETWEEN t.StartInterval AND t.EndInterval
 )
 SELECT [Interval From] = s, [Interval To] = e, [User], 
   [Elapsed Time] = CASE WHEN sr = 1 OR er = 1 THEN
       DATEDIFF(MINUTE, CASE WHEN sr = 1 THEN [Start Time] ELSE s END,
       CASE WHEN er = 1 THEN [End Time] ELSE e END)
   WHEN s = [End Time] THEN 0 ELSE @i END
 FROM n ORDER BY [User], [Interval From];

StackExchange Database Administrators Q#105793, answer score: 4