How do I modify this t-sql query to return the maximum value for different column names?

Question

I have the following query:

SELECT [Rate], [RateMon], [RateTue], [RateWed], [RateThu], [RateFri], [RateSat], [RateSun] FROM [Room] WHERE Id=@Id 

Instead of returning all columns, I just want to return Rate and the maximum value between RateMon, RateTue, RateWed, RateThu, RateFri, RateSat and RateSun, but it's hard for me because the column names are different.

The following is an example of returning a result:

100, 400, 400, 400, 400, 600, 600, 600

where 100 is speed and other values ​​correspond to Mon - Sun, but I want to return only 100 and 600 in this case.

Answer 1

 SELECT [Rate], (SELECT MAX(T.[Rate]) FROM (VALUES([RateMon]), ([RateTue]), ([RateWed]), ([RateThu]), ([RateFri]), ([RateSat]), ([RateSun])) AS T([Rate]) ) AS MaxRate FROM [Room] WHERE Id=@Id 

Answer 2

You can use something verbose:

 SELECT Rate, CASE WHEN RateMon>=RateTue AND RateMon>=RateWed AND RateMon>=RateThu AND RateMon>=RateFri AND RAteMon>=RateSat AND RateMon>=RateSun THEN RateMon WHEN RateTue>=RateMon AND RateTue>=RateWed AND RateTue>=RateThu AND RateTue>=RateFri AND RateTue>=RateSat AND RateTue>=RateSun THEN RateTue WHEN RateWed>=RateMon AND RateWed>=RateTue AND RateWed>=RateThu AND RateWed>=RateFri AND RateWed>=RateSat AND RateWed>=RateSun THEN RateWed WHEN RateThu>=RateMon AND RateThu>=RateTue AND RateThu>=RateWed AND RateThu>=RateFri AND RateThu>=RateSat AND RateThu>=RateSun THEN RateThu WHEN RateFri>=RateMon AND RateFri>=RateTue AND RateFri>=RateWed AND RateFri>=RateThu AND RateFri>=RateSat AND RateFri>=RateSun THEN RateFri WHEN RateSat>=RateMon AND RateSat>=RateTue AND RateSat>=RateWed AND RateSat>=RateThu AND RateSat>=RateFri AND RateSat>=RateSun THEN RateSat WHEN RateSun>=RateMon AND RateSun>=RateTue AND RateSun>=RateWed AND RateSun>=RateThu AND RateSun>=RateFri AND RateSun>=RateSat THEN RateSun END AS MaxRate FROM [Room] WHERE Id=@Id 

A lot of typing, but this is one of the possible answers. You can also do the following, which is slightly smaller:

 SELECT Rate, MAX(Rates.Rate) AS MaxRate FROM [Room], (SELECT RateMon AS Rate FROM [Room] WHERE Id=@Id UNION SELECT RateTue AS Rate FROM [Room] WHERE Id=@Id UNION SELECT RateWed AS Rate FROM [Room] WHERE Id=@Id UNION SELECT RateThu AS Rate FROM [Room] WHERE Id=@Id UNION SELECT RateFri AS Rate FROM [Room] WHERE Id=@Id UNION SELECT RateSat AS Rate FROM [Room] WHERE Id=@Id UNION SELECT RateSun AS Rate FROM [Room] WHERE Id=@Id ) AS Rates WHERE Id=@Id 

Of course, the right decision - This is the normalization of your database and the solution to this problem, since a simple combination and aggregate is enough.

Answer 3

You can disable days with the union subquery:

 select Rate , max(DayRate) from ( select ID, Rate, RateMon as DayRate from Room union all select ID, Rate, RateTue from Room union all select ID, Rate, RateWed from Room union all .... ) as SubQuery where ID = @ID group by Rate