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

    Unanswered: Do I need a while loop?

    Hello

    I have the following SQL which returns a time a person was in a particular department

    select
    Loc_name,
    ROW_NUMBER() OVER(ORDER BY LocationDate) as RowNum,
    CONVERT(varchar, Location_Date - Arrival_Date),108) as Time
    from dbo.locations
    where attendance_id = '123456'

    I get results as follows:

    Arrival Date Location Date RowNum Time Location
    26/02/2013 19:13 26/02/2013 19:20 1 00:07:00 Queue 1
    26/02/2013 19:13 26/02/2013 19:30 2 00:17:00 Dept 1
    26/02/2013 19:13 26/02/2013 21:10 3 01:57:00 Dept 2
    26/02/2013 19:13 27/02/2013 02:50 4 07:37:00 Left Department

    What I want to achieve is bypass the 01:57 and show 07:37 as the time the person spent in Dept2? If the last Location is "Left Department" then I only want to show the previous department in the location field

    I.e.


    Arrival Date Location Date RowNum Time Location
    26/02/2013 19:13 26/02/2013 19:20 1 00:07:00 Queue1
    26/02/2013 19:13 26/02/2013 19:30 2 00:17:00 Dept1
    26/02/2013 19:13 27/02/2013 02:50 3 07:37:00 Dept2



    Do I need a while loop to achieve this? and if so, could someone kindly help me get started with this as I am unsure of the syntex to write

    Other suggestions are very welcome

    Kind Regards

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What determines the "Dept1" value? Is that a constant, stored in another table, passed as a parameter, or the infamous "other" ?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2011
    Posts
    82
    Hello

    Thank you for your reply

    Location is a field which is a constant stored in another table

    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
  •