Insert Data in seconds between 30 minutes time interval I want to divide my time interval from the start time to end time.

The elapsed time should be divided across 30 minute time intervals so that for this input

08:05:13   12:09:49


I should get following output.
One way to achieve this involves creating a table of 48 daily half-hour period start times:

CREATE TABLE #HalfHours
(
    IntervalFrom time(3) PRIMARY KEY
);

INSERT #HalfHours
    (IntervalFrom)
SELECT 
    DATEADD
    (
        MINUTE, 
        30 * (SV.number - 1), 
        CONVERT(time(3), '00:00:00.000')
    )
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 48;


We can then use a query like the following:

-- The specified times
DECLARE
    @Start time(3) = '08:05:13',
    @End time(3) = '12:09:49';

SELECT
    HH.IntervalFrom,
    IntervalTo = CA.IntervalEnd,
    Elapsed =
        CASE
            -- Current period fully covered
            WHEN @Start &lt;= HH.IntervalFrom AND @End &gt;= CA.IntervalEnd 
                THEN 1800
            -- @Start within current period, @End not
            WHEN @Start &gt; HH.IntervalFrom AND @End &gt;= CA.IntervalEnd
                THEN 1800 - DATEDIFF(SECOND, HH.IntervalFrom, @Start)
            -- @End within current period, @Start not
            WHEN @Start &lt;= HH.IntervalFrom AND @End &lt; CA.IntervalEnd
                THEN DATEDIFF(SECOND, HH.IntervalFrom, @End)
            -- @Start and @End both within current period
            ELSE DATEDIFF(SECOND, @Start, @End)
        END
FROM #HalfHours AS HH
CROSS APPLY (VALUES(DATEADD(MINUTE, 30, HH.IntervalFrom))) AS CA (IntervalEnd)
WHERE
    @Start &lt; @End
    AND HH.IntervalFrom &lt; @End
    AND HH.IntervalFrom &gt;= 
        CASE 
            WHEN @Start &lt; CONVERT(time(3), '00:00:30.000') 
                THEN CONVERT(time(3), '00:00:00.000') 
            ELSE DATEADD(MINUTE, -30, @Start)
        END;






This can be generalized quite easily in case the input is more than a single pair of start/end times.