Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2005
    Posts
    91

    Unanswered: Average Turnaround Time - Datediff() Help

    Hi everyone, I need some help with creating a report that calculates the average turnaround time in days that it takes for units to return from trips destined to a location.

    The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below.


    UNIT -- TRIP -- START LOCATION --START DATE--FIN LOCATION--FIN DATE
    ================================================== =======
    U12 ----001 --- VANCOUVER -------FEB 10 ------ ONTARIO ----- FEB 15
    U10 ----002 --- VANCOUVER -------FEB 13 ------ ONTARIO ----- FEB 18
    U12 ----003 --- ONTARIO ----------MARCH 13 --- VANCOUVER -- MARCH 18
    U10 ----004 --- ONTARIO ----------MARCH 1 ---- VANCOUVER ---MARCH 6


    Unit U12 took 36 days to return back to Vancouver
    Unit U10 took 21 days to return back to Vancouver

    Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    try this, not a greate SQL though

    Code:
    select Route, avg(DaysTaken) as AvgDaysTaken
    from 	(
    	select A.Unit,(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+ '-'+(select top 1 Fin_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+'-'+(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip) as Route, cast(A.DaysTaken as decimal(9,2)) as DaysTaken
    	from 	(
    		select Unit,datediff(d,min(Start_date),max(fin_date)) DaysTaken from MyTable group by unit
    		) A
    	) B
    group by Route

Posting Permissions

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