Menu
I want to get the avarage rate for all 12 months from our rate table and divide it by months, i started writing an SQL select with case, but i seem to be doing something wrong in the "Between" part..here's my SQL
SELECT AVG(SELL_RATE),
AVG(BUY_RATE),
CASE MONTHS
WHEN RATE_DATE( BETWEEN '2010-01-01' AND '2010-01-31') THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
FROM RATE
WHERE CURRENCY_ID = CURRENCY -033'
341
CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component. You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN . This includes stringing together multiple conditional statements using AND and OR .
The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
using the equality operator (=). If you want to use other comparison operators such as greater than (>), less than (<), etc., you use the searched CASE expression. The CASE statement returns the result_1, result_2, or result_3 if the expression matches the corresponding expression in the WHEN clause.
Overview of the SQL Server BETWEEN operator In this syntax: First, specify the column or expression to test. Second, place the start_expression and end_expression between the BETWEEN and the AND keywords. The start_expression , end_expression and the expression to test must have the same data type.
Take out the MONTHS from your case, and remove the brackets... like this:
CASE
WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
You can think of this as being equivalent to:
CASE TRUE
WHEN RATE_DATE BETWEEN '2010-01-01' AND '2010-01-31' THEN 'JANUARY'
ELSE 'NOTHING'
END AS 'MONTHS'
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
