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.
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 ALLSELECT 'LAX', '02-May-2013',0,0,1,2,0 UNION ALLSELECT 'LAX', '03-May-2013',0,0,0,2,0 SELECT * FROM #delaystatsDROP TABLE #delaystatsThe 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 beAirport Date MinDelayLAX 01-May-2013 Delay1 LAX 02-May-2013 Delay3LAX 03-May-2013 Delay4Any 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 MinDelayFROM #delaystats |
|
|
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'unionselect 1, 'One hour delay'etcAlso would encourage you to create an airport table similar to the above onThen 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 |
|
|
|
|
|
|
|