# Thread: Help with calculating duration time

1. Registered User
Join Date
Feb 2004
Posts
106

## Unanswered: 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?

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Blind code, but give it a try:
Code:
```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.

3. Registered User
Join Date
Feb 2004
Posts
106
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.

4. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
This formula will concatenate datetime to whole dates:

select dateadd(d, datediff(d, 0, [Yourdate]), 0) as WholeDate

5. Registered User
Join Date
Feb 2004
Posts
106
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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•