min function & distinct columns selection

[omar999]

hi guys i have a table like this

ID | Flight_Route | Price_Band_1 | Month_Band_1 | Date_Band_1 | Price_Band_2 | Month_Band_2 | Date_Band_2 | Price_Band_3 | Month_Band_3 | Date_Band_4 | Price_Band_4 | Month_Band_4 | Date_Band_4
1         TEST1          99           MAY           12, 19, 31         199         JUNE         12, 19, 31        199         JULY        12, 19, 31        199        AUG        12, 19, 31

and I basically am trying to select the cheapest price from price_band_1, price_band_2, price_band_3, price_band_4, price_band_5 and then I want to select the corresponding month and dates.

I can select the cheapest price like this

Select CheapestPrice = MIN(CheapestPrice) from  & _
(Select MIN(Price_Band_1) As CheapestPrice from mytablename where Id = 1  & _
UNION ALL  & _
Select MIN(Price_Band_2) from mytablename where Id = 1  & _
UNION ALL  & _
Select MIN(Price_Band_3) from mytablename where Id = 1  & _
UNION ALL  & _
Select MIN(Price_Band_4) from mytablename where Id = 1  & _
UNION ALL  & _
Select MIN(Price_Band_5) from mytablename where Id = 1 )x

but I'm really confused as to how to select the corresponding month & dates?

this surely cant just be a case of attaching another union select statement to select the month & date columns. I'm guessing it needs some sort of If statement attached?

IF MINPRICE = PRICEBAND 1
DO
Select MONTH_Band_1, DATE_Band_1??

any ideas?

thanks in advance
Omar.

[Jerry Winston]

This is a classic example of the fundamental problems associated with generating useful statistics from numbered columns. Generally, when you start to consider creating Column1, Column2, Column3,... fields in a table, there is a good chance there is an opportunity to create a related table. IMO you need a band price/date table with a foreign key relationship to the band table. The band price/date table should only contain fields related to price, date, month and such.

That's my two cents. Here's a solution to your current situation:

DECLARE @tmpBandTable TABLE (ID,Price_Band,Month_Band,Date_Band)

INSERT INTO @tmpBandTable
SELECT ID,Price_Band_1, Month_Band_1,Date_Band_1
FROM mytablename

INSERT INTO @tmpBandTable
SELECT ID,Price_Band_2, Month_Band_2,Date_Band_2
FROM mytablename

INSERT INTO @tmpBandTable
SELECT ID,Price_Band_3, Month_Band_3,Date_Band_3
FROM mytablename

INSERT INTO @tmpBandTable
SELECT ID,Price_Band_4, Month_Band_4,Date_Band_4
FROM mytablename

INSERT INTO @tmpBandTable
SELECT ID,Price_Band_5, Month_Band_5,Date_Band_5
FROM mytablename


SELECT TOP 1 * FROM @tmpBandTable
ORDER BY Price_Band ASC

This solution is static. So it won't work propperly if you add another Price_Band_#,Mo nth_Band_#,Date _Band_# field triplet. If you need a dynamic solution i'd suggest using the system views. Here's a start:

SELECT S.[name], T.[name], C.[name] FROM sys.Columns C
INNER JOIN
sys.Tables T
ON
C.object_id=T.object_id
INNER JOIN
sys.Schemas S
ON
T.schema_id=S.schema_id
WHERE
T.[name] = 'MyTableName'
AND
S.[name] = 'MySchema'
AND
C.[name] LIKE '%_Band_%'