Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: Help with the MIN function

    Hello,

    I'm still a beginner and need a little help please. I have a table tm_Quotes joined with a table tm_QuoteLegs by QuoteID. I only want information (rows) for the first leg (earliest departure date/time) within each quote.

    Using the below query I get the desired results but when I uncomment the expression to get the Origin and Destination airport I get almost twice the rows containing Origin and Destination airports for QuoteLegs other than only the first (MIN) leg as desired. How can I fix this?

    SELECT

    DISTINCT Q.QuoteID
    ,MIN(QL.departureTime) OVER (Partition by Q.QuoteID) as TripDate
    --,Orig.AirportICAOCode as Origin
    --,Dest.AirportICAOCode as Destination

    FROM tm_Quotes as Q

    INNER JOIN tm_QuoteLegs as QL ON QL.QuoteID=Q.QuoteID and Q.StatusTypeID=13
    INNER JOIN tl_Airports as Orig ON Orig.AirportID=QL.OriginAirportID
    INNER JOIN tl_Airports as Dest ON Dest.AirportID=QL.DestinationAirportID

    Group by Q.QuoteID, Orig.AirportICAOCode, Dest.AirportICAOCode

    CURRENT OUTPUT

    QUOTEID TripDate Origin Destination
    1 2012-11-1 12:00 KSNA KLAS
    1 2012-11-1 12:00 KLAS KSNA
    2 2012-11-1 09:00 KVNY KSFO
    2 2012-11-1 09:00 KSFO KVNY

    (note: minimum trip date is applied to both legs within the same QuoteID when in reality legs other than the first leg have a later departure time)


    DESIRED OUTPUT

    QUOTEID TripDate Origin Destination
    1 2012-11-1 12:00 KSNA KLAS
    2 2012-11-1 09:00 KVNY KSFO


    Thanks!

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    If I'm following correctly, you will never be able to obtain your desired output.

    You state in your note that
    minimum trip date is applied to both legs within the same QuoteID when in reality legs other than the first leg have a later departure time
    This means that the database has no way of "knowing" which is the true minimum date. It's therefore returning all records that have the minimum departure date when grouped by the other fields.

    Why is your data being deliberately corrupted in this manner?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jan 2012
    Posts
    7
    Regarding the note, that result is not desired so the query must be wrong. I only want to see data for the first leg of each quote identified by the quote leg with the earliest departure time. As I said, I am able to do this until I uncomment the lines to add the quote leg origin and destination. That's when I get current output as described.

    Thanks for your help.

    Steve

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Ah, I think I see.

    In that case, you possibly want a subquery to identify the minimum trip date for each QuoteID in tm_QuoteLegs, and build the rest of the query around that.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Jan 2012
    Posts
    7
    Can you provide an example of how that would look?

    Thanks

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

    Try:

    Code:
    with CTE as
    (
        select 
            Q.QuoteID,
            QL.departureTime,
            QL.OriginAirportID,
            QL.DestinationAirportID,
            ROW_NUMBER() OVER(PARTITION BY Q.QuoteID ORDER BY QL.departureTime) as RowNum
        from tm_Quotes as Q
        INNER JOIN tm_QuoteLegs as QL ON QL.QuoteID = Q.QuoteID
        where Q.StatusTypeID = 13
    )
    
    SELECT 
        c.QuoteID
        c.departureTime as TripDate,
        Orig.AirportICAOCode as Origin
        Dest.AirportICAOCode as Destination
    FROM CTE as c
    INNER JOIN tl_Airports as Orig ON Orig.AirportID = c.OriginAirportID
    INNER JOIN tl_Airports as Dest ON Dest.AirportID = c.DestinationAirportID
    where c.RowNum = 1
    Hope this helps.

  7. #7
    Join Date
    Jan 2012
    Posts
    7
    Perfect! Thank you!

Posting Permissions

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