• abhas (4/9/2013)


    Thank you Chris.

    I found below code some where else which is resemble to my requirement. Now i want to insert whole result set into my database table. ๐Ÿ™‚

    DECLARE @t TABLE

    (OfficeId INT, WeekdayId INT, StartTime TIME, EndTime TIME)

    INSERT INTO @t

    SELECT 1, 1001, '14:30', '16:30'

    ;WITH Tally (n) AS (

    SELECT TOP 100 15*(ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)

    FROM sys.all_columns)

    SELECT OfficeID, WeekdayID

    ,TSStart=DATEADD(minute, n, StartTime)

    ,TSEnd=DATEADD(minute, n + 15, StartTime)

    ,Timeslot=CONVERT(VARCHAR(100), DATEADD(minute, n, StartTime), 0) + ' - ' +

    CONVERT(VARCHAR(100), DATEADD(minute, n + 15, StartTime), 0)

    FROM @t

    CROSS APPLY (

    SELECT n

    FROM Tally

    WHERE n BETWEEN 0 AND DATEDIFF(minute, StartTime, DATEADD(minute, -15, EndTime))) a

    ORDER BY OfficeID, WeekdayID, TSStart

    Thanks

    Abhas

    That's functionally equivalent to the code I wrote for you - the execution plans may even be the same.

    โ€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.โ€ - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden