Help with calculating duration time

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?Blind code, but give it a try: create table #Blocks
(Machine_No int,
Start_Time datetime,
End_Time datetime)

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets) FirstTickets
on Tickets.Machine_No = FirstTickets.Machine_No
and Tickets.Start_Time = FirstTickets.StartTime

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update #Blocks
set End_Time = max(End_Time)
from #Blocks
inner join Tickets
on #Blocks.Machine_No = Tickets.Machine_No
and Tickets.Start_Time between #Blocks.Start_Time and #Blocks.End_Time
and Tickets.End_Time > #Blocks.End_Time

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets
where Start_Time >
(select max(End_Time)
from #Blocks
where #Blocks.Machine_No = Tickets.Machine_No) NextTickets
on Tickets.Machine_No = NextTickets.Machine_No
and Tickets.Start_Time = NextTickets.StartTime
end

select Machine_No,
sum(datediff(s, Start_Time, End_Time)/3600.00 as Hours
from #Blocks
group by Machine_No
If you can deal with discrete time segments (say, 1 minute increments) and place an upper limit on the date range, you may be able to do this with a simpler query using a table of integers.|||I really think I can use this (with minor modifications). How can I make this work so that it only compares the records within the same day and not all the records? Thank you.|||This formula will concatenate datetime to whole dates:

select dateadd(d, datediff(d, 0, [Yourdate]), 0) as WholeDate|||How can I modify the following solution so that it only captures the duration time for range of 7 am - 11 pm spanning multiple days? Right now, it works only for 24 hr period. Here is my code:

SELECT dbo.tbl_installed_ATMs.Term_ID, FirstTickets.Object_Key, FirstTickets.Start_Time, MAX(dbo.tbl_install_tickets.END_TIME) AS End_Time,
CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110) AS Start_Date, CONVERT(varchar(12), dbo.tbl_install_tickets.END_TIME, 110)
AS End_Date, dbo.tbl_status_code_key.DESCRIPTION
INTO dbo.Blocks
FROM dbo.tbl_install_tickets INNER JOIN
(SELECT Object_Key, MIN(Start_Time) Start_Time
FROM tbl_install_tickets
GROUP BY Object_Key) FirstTickets ON dbo.tbl_install_tickets.OBJECT_KEY = FirstTickets.Object_Key AND
dbo.tbl_install_tickets.START_TIME = FirstTickets.Start_Time INNER JOIN
dbo.tbl_status_code_key ON dbo.tbl_install_tickets.STATUS_CODE_KEY = dbo.tbl_status_code_key.LINK INNER JOIN
dbo.tbl_installed_ATMs ON FirstTickets.Object_Key = dbo.tbl_installed_ATMs.Object_Key
GROUP BY FirstTickets.Object_Key, FirstTickets.Start_Time, CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110), CONVERT(varchar(12),
dbo.tbl_install_tickets.END_TIME, 110), dbo.tbl_status_code_key.DESCRIPTION, dbo.tbl_installed_ATMs.Term_ID

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update Blocks
set End_Time = (Select max(Blocks.End_Time)
from Blocks
inner join tbl_install_tickets
On Blocks.Object_Key = tbl_install_Tickets.Object_Key
and tbl_install_Tickets.Start_Time between Blocks.Start_Time and Blocks.End_Time
and tbl_install_Tickets.End_Time > Blocks.End_Time
)
from Blocks
end

SELECT Term_ID, CONVERT(varchar(12), Start_Time, 110) AS Date, DESCRIPTION AS Ticket_type, SUM(DATEDIFF(s, Start_Time, End_Time) / 3600.00)
AS Duration
FROM dbo.Blocks
GROUP BY Term_ID, CONVERT(varchar(12), Start_Time, 110), DESCRIPTION
GO