Please, read comments in the sample code and try this:
DECLARE @srcTable TABLE (MachineId INT, startTime DATETIME, endTime DATETIME)
-destination table
DECLARE @dstTable TABLE (MachineId INT, HourNo INT, startHour DATETIME, endHour DATETIME)
INSERT INTO @srcTable (MachineId, startTime, endTime)
VALUES(1, '1998-06-06 09:00:00', '1998-06-06 11:00:00'),
(2, '1998-06-06 10:00:00', '1998-06-06 12:00:00'),
(3, '1998-06-06 11:00:00', '1998-06-06 12:00:00')
;WITH MyHours AS
(
SELECT MachineId, 1 As HourNo, starttime, endTime, startTime as startHour, DATEADD(hour,1,starttime) AS endHour
FROM @srcTable
WHERE DATEADD(hour,1,starttime)<= endTime
UNION ALL
SELECT MachineId, HourNo + 1 AS HourNo, starttime, endTime, endHour as startHour, DATEADD(hour,1,endHour) AS endHour
FROM MyHours
WHERE DATEADD(hour,1,endhour)<= endtime
)
INSERT INTO @dstTable (MachineId, HourNo, startHour,endHour )
SELECT MachineId, HourNo, startHour,endHour
FROM MyHours
ORDER BY MachineId, HourNo
SELECT t1.MachineId AS MachineId1, t1.HourNo AS HourNo1, t2.MachineId AS MachineId2, t2.HourNo AS HourNo2, t1.startHour, t1.endHour
FROM @dstTable AS t1 INNER JOIN @dstTable AS t2 ON t1.startHour = t2.startHour AND t1.endHour = t2.endHour AND t1.MachineId < t2.MachineId
ORDER BY t1.MachineId, t1.HourNo
Result:
MachineId1 HourNo1 MachineId2 HourNo2 startHour endHour
1 2 2 1 1998-06-06 10:00:00.000 1998-06-06 11:00:00.000
2 2 3 1 1998-06-06 11:00:00.000 1998-06-06 12:00:00.000