SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

opensourcederry
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)
,thedate SMALLDATETIME
,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?

thanks,

osd

James K
Flowing Fount of Yak Knowledge

3742 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.
SELECT
	airport,
	theDate, 
	CASE
		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
FROM	
	#delaystats	
Go to Top of Page

yosiasz
Flowing Fount of Yak Knowledge

USA
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'
union
select 1, 'One hour delay'

etc

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  
 New 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.05 seconds. Powered By: Snitz Forums 2000