MIN Function Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MIN Function Issue

Author

Topic

stharish
Starting Member

43 Posts

Posted - 2012-11-01 : 20:22:08

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

stharish
Starting Member

43 Posts

Posted - 2012-11-01 : 23:20:51

Is anyone able to help or maybe point me in the right direction? Thanks!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-02 : 03:45:15

can u post structure of tables?

--
Chandu

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-11-02 : 03:51:35

please also post the sample data.

is this a round trip ?


QUOTEID TripDate Origin Destination
1 2012-11-1 12:00 KSNA KLAS
1 2012-11-1 12:00 KLAS KSNA

So how do you identify which is the first leg ?

KH
[spoiler]Time is always against us[/spoiler]

stharish
Starting Member

43 Posts

Posted - 2012-11-02 : 10:13:51

I will post table structure and sample data shortly. Yes, this would be a round trip but as you can see the minimum departure time is being applied to both legs which is not desired behavior.

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

stharish
Starting Member

43 Posts

Posted - 2012-11-02 : 12:14:33

Table data below. Should I maybe be using a subquery?

QuoteLegs Table (sample)

QuoteLegID QuoteID OriginAirportID DestinationAirportID DepartureTime ArrivalTime FlightTime TotalDistance
3361 1 8062 1658 2012-01-25 10:00:00.0000000 -08:00 2012-01-25 11:07:00.0000000 -08:00 01:07:00.0000000 198
3362 2 1658 8062 2012-01-24 13:00:00.0000000 -08:00 2012-01-24 13:57:00.0000000 -08:00 00:57:00.0000000 198
3363 3 1658 8062 2012-01-24 15:00:00.0000000 -08:00 2012-01-24 15:57:00.0000000 -08:00 00:57:00.0000000 198
3364 4 1658 8084 2012-02-02 19:00:00.0000000 -08:00 2012-02-02 20:31:00.0000000 -08:00 01:31:00.0000000 323
3365 4 8084 1658 2012-02-05 19:00:00.0000000 -08:00 2012-02-05 20:18:00.0000000 -08:00 01:18:00.0000000 323

Quotes Table (sample)

QuoteID QuoteNumber ContractID AuthorizedUserID StatusTypeID StatusDate
1 01-001 139 578 13 2012-01-24 13:59:48.013 1 2012-01-24
2 02-001 654 2749 13 2012-01-24 16:15:32.853 1 2012-01-24
3 03-001 139 578 11 2012-01-24 20:19:18.817 1 2012-01-24
4 04-001 922 3904 13 2012-02-01 15:15:11.260 1 2012-01-25
5 05-001 139 578 12 2012-01-25 16:40:56.377 1 2012-01-25

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 12:22:55

If I am reading this right, quotes 1,2, and 3 has one leg each and quote 4 has two legs. Quote 5 has zero legs.

What is the output you are expecting to see for this sample input data?

stharish
Starting Member

43 Posts

Posted - 2012-11-02 : 12:40:31

Sorry, I should have left out QuoteID 5.

After joining both tables and the Airports table by AirportID I would expect to see only 4 rows with QuoteID 4 showing only data for first Quote Leg within that Quote:

QuoteLegID QuoteID Origin Destination DepartureTime ArrivalTime FlightTime TotalDistance
3361 1 KSFO KSMF 2012-01-25 10:00:00.0000000 -08:00 2012-01-25 11:07:00.0000000 -08:00 01:07:00.0000000 198
3362 2 KVNY KLAS 2012-01-24 13:00:00.0000000 -08:00 2012-01-24 13:57:00.0000000 -08:00 00:57:00.0000000 198
3363 3 KSAN KONT 2012-01-24 15:00:00.0000000 -08:00 2012-01-24 15:57:00.0000000 -08:00 00:57:00.0000000 198
3364 4 KDEN KHOU 2012-02-02 19:00:00.0000000 -08:00 2012-02-02 20:31:00.0000000 -08:00 01:31:00.0000000 323

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-02 : 12:55:08

First, you have to identify one (or more) columns in the data that you can use to order the legs of a given QuoteID. In my example below, I am assuming that it is QuoteLegId (see in red). If it is something else - for example, if it is departuretime, replace the red QuoteLegId with departuretime.

SELECT * FROM
(
	SELECT
		QuoteLegID,
		QuoteID	,
		OriginAirportID ,
		DestinationAirportID	 ,
		DepartureTime	,
		ArrivalTime	,
		FlightTime	,
		TotalDistance,
		ROW_NUMBER() OVER (PARTITION BY QuoteID ORDER BY QuoteLegId) AS RN
	FROM 
		QuoteLegsTable
)s
WHERE RN = 1;

stharish
Starting Member

43 Posts

Posted - 2012-11-02 : 14:11:47

This is perfect! Thank you SO MUCH!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources