How can I use cast in SQL with multiple column selections?

Question

I tried this to get our revenue:

SELECT 
    CAST(stays_in_week_nights AS int) + 
        CAST(stays_in_weekend_nights AS int) * 
        CAST(adr AS int) AS revenue
FROM 
    Hotels

I expected to get a number as the result of the calculation - but instead, I get this error:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value '111.6' to data type int.

Answer 1

cast is as a decimal instead of as an int. Assuming it is the adr field that is the problem, it would be

SELECT  
   (CAST(stays_in_week_nights AS int) + CAST(stays_in_weekend_nights AS int))
   * CAST(adr AS decimal(10,2)) AS revenue
 FROM Hotels

What you would need for the two parameters in decimal(10,2) would depend on your data. Decimal(10,2) would give you 8 places before the decimal point and 2 after.

Also, you may want to add an extra set of parentheses to make sure you are calculating it correctly. Do you want (week+weekend) * adr or week + (weekend*adr)

What you are currently going to get is the second. Even if that is what you want, adding parentheses would clarify to the next developer that that is what you intended to do