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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find lowest populated field, not min value
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

12 Posts

Posted - 07/01/2013 :  17:56:25  Show Profile  Reply with Quote
Hi all,

I have a tricky one.

I have a dataset as below which deals with airport delays.

CREATE TABLE #delaystats
airport VARCHAR(10)
,delay1 INT
,delay2 INT
,delay3 INT
,delay4 INT
,delay5 INT


INSERT INTO #delaystats (airport,thedate,delay1,delay2,delay3,delay4,delay5)

SELECT 'LAX', '01-May-2013',2,0,1,4,1 UNION ALL
SELECT 'LAX', '02-May-2013',0,0,1,2,0 UNION ALL
SELECT 'LAX', '03-May-2013',0,0,0,2,0

SELECT * FROM #delaystats

DROP TABLE #delaystats

The dataset is quite simple and contains an airport code, date and field-names which represent flight delays. So delay1 contains a number representing how many flights where delays by 1 hour on that day, delay2 is 2 hours and so on.

What I need to do is for each date return the field that represents the minimum delay for that day. Note it is not how many flights where delayed but the minimum delay for that day.

So the result on the above dataset would be

Airport Date MinDelay
LAX 01-May-2013 Delay1
LAX 02-May-2013 Delay3
LAX 03-May-2013 Delay4

Any ideas on how to achieve this?



James K
Flowing Fount of Yak Knowledge

3873 Posts

Posted - 07/01/2013 :  18:24:54  Show Profile  Reply with Quote
You can always use the "brute force approach", which in fact, is what I would recommend if you had only a handful of delay columns. If you have a lot many delay columns, or if you want to go for elegance rather than readbility, we can think of other ways.
		WHEN delay1 > 0 THEN 'Delay1'
		WHEN delay2 > 0 THEN 'Delay2'
		WHEN delay3 > 0 THEN 'Delay3'
		WHEN delay4 > 0 THEN 'Delay4'
		WHEN delay5 > 0 THEN 'Delay5'
		ELSE 'No delays'
	END AS MinDelay
Go to Top of Page

Flowing Fount of Yak Knowledge

1635 Posts

Posted - 07/01/2013 :  21:01:29  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
i would highly recommend a different design. add a Delay_List(DelayID, DelayValue, DelayDescr)
insert into Delay_List(DelayVaue, DelayDescr)
select 2, 'Two hours delay'
select 1, 'One hour delay'


Also would encourage you to create an airport table similar to the above on
Then create the delayStats table that references the above two tables via FK reference, then you can do clean queries and get more meaningful stats from your data. Might seem overkill but in the long run you will see the benefits

If you don't have the passion to help people, you have no passion
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000