I have a tricky one.
I have a dataset as below which deals with airport delays.
CREATE TABLE #delaystats
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?