Using BETWEEN in CASE SQL statement

Question

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

User · Answer

You do not specify why you think it is wrong but I can se two dangers:

BETWEEN can be implemented differently in different databases sometimes it is including the border values and sometimes excluding, resulting in that 1 and 31 of january would end up NOTHING. You should test how you database does this.

Also, if RATE_DATE contains hours also 2010-01-31 might be translated to 2010-01-31 00:00 which also would exclude any row with an hour other that 00:00.

User · Answer

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

[sql] Using BETWEEN in CASE SQL statement

Examples related to sql

Examples related to sql-server