Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2011
    Posts
    82

    Unanswered: Help with Time Difference Between sequence dates

    Hello

    It has been a while since I have used SQL server and require some help with regards to showing a time difference between a start and end time, with only 1 date to determine these times

    SQL
    select
    Arrival_date
    locationdate as LocDate,
    locationid as Loc_ID,
    locations.loc_name as Location_Name
    from current_locations
    left outer join locations on
    locations.loc_id = current_locations.location_id
    where current_locations.Attend_ID = '1234567'

    Results
    Arrival Date LocDate Loc_ID loc_name
    26/02/2013 19:21 26/02/2013 19:27 1270 Queue
    26/02/2013 19:21 26/02/2013 19:34 1278 Dept 1
    26/02/2013 19:21 26/02/2013 21:10 222 Dept 2
    26/02/2013 19:21 27/02/2013 02:56 31 Left Department

    What I want to acheive is the following where the Start location date is either the arrival date or the next location date and the endLocDate is the next finish date.


    StartLocDate EndLocDate Time Difference Dept
    26/02/2013 19:21 26/02/2013 19:27 00/01/1900 00:06 Queue
    26/02/2013 19:27 26/02/2013 19:34 00/01/1900 00:07 Dept 1
    26/02/2013 19:34 27/02/2013 02:56 00/01/1900 07:22 Dept 2

    Can anyone provide some advice on how I would go about achieving this?


    Thanks
    Helen

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Maybe this will help you get started:

    Code:
    SELECT  CONVERT(varchar, (LocationDate - Arrival_Date),108), 
    	DATEDIFF(ss, Arrival_Date, LocationDate) / 86400 as Days,
    	DATEDIFF(ss, Arrival_Date, LocationDate) / 3600 as Hours,
    	DATEDIFF(ss, Arrival_Date, LocationDate) % 3600 / 60 as Minutes,
    	DATEDIFF(ss, Arrival_Date, LocationDate) % 3600 % 60 as Seconds

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    Thank you for this,

    It really does help me get started!

    Thanks
    Helen

  4. #4
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try:

    Code:
    with CTE_R as
    (
        select
            Arrival_date,
            locationdate as LocDate,
            locationid as Loc_ID,
            locations.loc_name as Location_Name,
            ROW_NUMBER() OVER(ORDER BY LocDate) as RowNum
        from current_locations 
        left outer join locations 
            on locations.loc_id = current_locations.location_id
        where current_locations.Attend_ID = '1234567'
    )
    
    select
        case when r.RowNum = 1 then r.Arrival_Date else p.LocDate end as StartLocDate,
        r.LocDate as EndLocDate,
        r.LocDate - case when r.RowNum = 1 then r.Arrival_Date else p.LocDate end as DiffDate,
        r.Loc_ID,
        r.loc_name
    from CTE_R as r
    left join CTE_R as p
        on p.RowNum = r.RowNum - 1
    Hope this helps.

  5. #5
    Join Date
    Dec 2011
    Posts
    82
    Hello

    Thank you very much for your code, this does help me and it definately gives me the starting point

    I don't suppose you are able to offer advice on the following;

    If the last location is "Left" then I want to count the last 2 row numbers together

    I have been playing with this since 9am this morning, but am no closer to getting the last 2 row numbers together when the last location is "Left"

    I appreciate all your help so far, so no worries if not

    Thanks
    Helen

Posting Permissions

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