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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Find lowest populated field, not min value

Author  Topic 

opensourcederry
Starting Member

12 Posts

Posted - 2013-07-01 : 17:56:25
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 18:24:54
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
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2013-07-01 : 21:01:29
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
   

- Advertisement -