quote:

---

Originally posted by dpais

yes they are ... thanks.

---

SELECT IDENTITY(int,1,1) AS ID,
DATE,CODES,START_TIME,END_TIME
INTO #Temp
FROM YourTable
ORDER BY DATE,START_TIME

SELECT CONVERT(varchar(11),DATE,101) AS DATE,
CONVERT(varchar(5),DATEADD(hh,v.number-1,STARTTIME),108) AS STARTTIME,
CONVERT(varchar(5),DATEADD(hh,v.number,STARTTIME),108) AS ENDTIME,
CODES
FROM #Temp t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(hh,v.number,STARTTIME)<=ENDTIME

UNION 

SELECT
CONVERT(varchar(11),SELECT DATEADD(hh,v.number-1,t.StartDate),101),
CONVERT(varchar(5),DATEADD(hh,v.number-1,t.StartDate),108),
CONVERT(varchar(5),DATEADD(hh,v.number,t.EndDate),108),
NULL
FROM
(SELECT CAST(CAST(t1.DATE AS varchar(11))+ ' '+CAST(t1.END_TIME AS varchar(4)) AS datetime) AS StartDate,
CAST(CAST(t2.DATE AS varchar(11))+ ' '+CAST(t2.START_TIME AS varchar(4)) AS datetime) AS EndDate
FROM #Temp t1
INNER JOIN #Temp t2
ON t2.ID=t1.ID+1) t
CROSS JOIN master..spt_values v
WHERE v.type='p'
AND DATEADD(hh,v.number,t.StartDate)<=t.EndDate
ORDER BY DATE,STARTTIME