SQLTeam.com | Weblogs | Forums

Issue Using a Case Statement in a Where Clause

#1

I am trying to make one select from these two senarios but it is not working:

Select Sum(CASE When H_HA_ID = 6 Then 1 Else 0 End) As CallsMade,
Sum(CASE When H_HA_ID = 3 Then 1 Else 0 End) As Emails,
Sum(CASE When H_HA_ID = 10 Then 1 Else 0 End) As SMS
From History Where H_IDX = 192652 And H_DateTime > DATEADD(DAY, -30, GETDATE())

Select Sum(CASE When H_HA_ID = 6 Then 1 Else 0 End) As CallsMade,
Sum(CASE When H_HA_ID = 3 Then 1 Else 0 End) As Emails,
Sum(CASE When H_HA_ID = 10 Then 1 Else 0 End) As SMS
From History Where H_IDX = 192652 And H_DateTime >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),'01')

This is my attempt:

Declare @DateChoice VarChar (25)
Set @DateChoice = '30 Days'

Select Sum(CASE When H_HA_ID = 6 Then 1 Else 0 End) As CallsMade,
Sum(CASE When H_HA_ID = 3 Then 1 Else 0 End) As Emails,
Sum(CASE When H_HA_ID = 10 Then 1 Else 0 End) As SMS
From History Where
Case @DateChoice
When '30 Days' Then H_IDX = 192652 And H_DateTime > DATEADD(DAY, -30, GETDATE())
Else
H_IDX = 192652 And H_DateTime >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),'01')
End

Please help with my coding issue.

#2 
Where (@DateChoice = '30 Days' and H_IDX = 192652 And 
    H_DateTime > DATEADD(DAY, -30, CAST(GETDATE() AS date)))
or (ISNULL(@DateChoice, '') <> '30 Days' and H_IDX = 192652 And 
    H_DateTime >= DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),'01'))
#3

A CASE expression must yield only a single value. It cannot contain keywords (like "and") or conditions (like "=" or ">").

#4

Thank you so much for the answer, it works perfectly.
I really appreciate your help.

#5

Instead of using a case expression in your where clause - you should calculate the start date and use that variable in your query.

Declare @DateChoice varchar(25) = '30 Days';
Declare @StartDate date = iif(@DateChoice = '30 Days', dateadd(day, -30, cast(getdate() As date)), datefromparts(year(getdate()), month(getdate()), 1);

Select ...
  From ...
 Where H_IDX = 192652
   And H_DateTime >= @StartDate

If you don't want to use another variable - then rewrite the case expression to return the correct starting date only:

Where H_IDX = 192652
  And H_DateTime >= CASE WHEN @DateChoice = '30 Days' THEN DATEADD(day, -30, cast(getdate() as date)
                         ELSE datefromparts(year(getdate()), month(getdate()), 1)
                     END
#6

Thanks for your help, this worked well. :grinning: