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