Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015
    Posts
    1

    Question Unanswered: Issue with Query

    We are having trouble figuring out how to create a view for this scenario:

    We have a status log table that holds an order number, statusdatetime, and statuscode. This table will have multiple status' for the same order number. I want to create a view that will give me the most current status (by statusdatetime) of each order number. This view would show: order number, statusdatetime, and statuscode.

    Here is a sample of the data:

    Order number StatusDateTime StatusCode
    12345 12/15/2014 15:00 CREATE
    12345 12/15/2014 16:30 CONFIRMED
    45678 12/16/2014 08:00 CREATE
    98764 12/18/2014 12:00 CREATE
    98764 12/19/2014 08:00 CONFIRMED
    45678 12/17/2014 09:30 CONFIRMED
    45678 12/19/2014 15:30 IN-TRANSIT

    So my view should result in :

    Order number StatusDateTime StatusCode
    12345 12/15/2014 16:30 CONFIRMED
    98764 12/19/2014 08:00 CONFIRMED
    45678 12/19/2014 15:30 IN-TRANSIT


    Thank you in advance

    Aero

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT p.[Order number], p.StatusDateTime, p.StatusCode
       FROM Orders AS p
       WHERE  p.StatusDateTime = (SELECT Max(z.StatusDateTime)
          FROM Orders AS z
          WHERE  z.[Order number] = p.[Order number])
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT order_number
         , status_datetime
         , status_code
    FROM   (
            SELECT order_number
                 , status_datetime
                 , status_code
                 , Row_Number() OVER (PARTITION BY order_number ORDER BY status_datetime DESC) As sequence
            FROM   orders
           ) As x
    WHERE  sequence = 1
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm. Not a lot of skin left on that cat for me to work with. I don't especially feel like writing up the cursor version of the solution. ;-)

  5. #5
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

Posting Permissions

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