Error occuring on sum case statement using a date between

Question

I am trying to create a statement which will retrieve some stats from the the database.

I am trying to use a SUM case statement to retrieve the number of records which have a join date between start and end of the previous year, however I keep getting the error message:

Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'BETWEEN'

SELECT
    P.ProductName
    ,SUM(case M.IsActive when 1 then 1 else 0 end) ACTIVE
    ,SUM(case M.IsActive when 0 then 1 else 0 end) INACTIVE
    ,SUM(case m.joindate WHEN m.joindate BETWEEN DATEADD(yy, DATEDIFF(yy, 0,    GETDATE()) - 1, 0) AND DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0,  GETDATE()), 0)) THEN 1 ELSE 0) AS '2018'
FROM 
    table1 M
LEFT JOIN 
    table2 P ON P.ProductID = M.ProductID
GROUP BY 
    P.ProductName

I have did look the error up online and it was recommended to try to take out the m.joindate so it would be SUM(Case WHEN but this didn't work either and produced the same message.

Does anyone know why this message is appearing? Is it that you cannot use BETWEEN within a case statement or have i written it incorrectly?

Thank you in advance

Answer 1

You phrased the third CASE expression wrongly. Here is one way to do it:

COUNT(CASE WHEN m.joindate BETWEEN
    DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) - 1, 0) AND
    DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0,  GETDATE()), 0))
    THEN 1 END) AS [2018]

Note that I used COUNT instead of SUM, which eliminates the need for an explicit ELSE condition.

The form of the CASE expression which you were using only works if the predicate values are constants (not a BETWEEN clause). So, for a contrived example, the following would work:

CASE color
    WHEN 'red'   THEN 'R'
    WHEN 'blue'  THEN 'B'
    WHEN 'green' THEN 'G' END AS label

This version is useful because it does not require repeating the value being checked multiple times.