Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    106

    Question 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. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    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. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This formula will concatenate datetime to whole dates:

    select dateadd(d, datediff(d, 0, [Yourdate]), 0) as WholeDate
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    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
  •