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.
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