How to use case statement by comparing two string inside where clause in sql

Question

I need to compare two strings if they are same means needs to take one where condition else takes another condition but this code not worked for me. Any corrections and suggestions welcomed. here I have tried so far.

declare @s1 varchar(10) = '2018',@s2 varchar(10) = '2019'
Select  PersonalAbwesenheiten.PersonalNr,sum(PersonalAbwesenheiten.AnzahlTage) as totaltakenleaves from PersonalAbwesenheiten 
  Join Personal On personal.PersonalNr = PersonalAbwesenheiten.personalNr and PersonalAbwesenheiten.PersonalNr = 4 and PersonalAbwesenheiten.Status = 4
  and  PersonalAbwesenheiten.Abwesenheitstyp = 1
  where

   case when @s1 = @s2 Then

   '2019-01-01' <= DatumBis And DatumVon <= '2019-12-31'

   when @s1 <> @s2 Then
   '2018-12-30' <= DatumBis And DatumVon <= '2019-01-05'
   end


    group by PersonalAbwesenheiten.PersonalNr

In your example, the condition `@s1 = @s2` does not depend on the query result itself. Is that representative for your use case? If so, then just replace the date literals with two new variables whose values depend on the result of `@s1 = @s2`. — Ruud Helderman, Feb 21 '19 at 14:20

score 2 · Accepted Answer · answered Feb 21 '19 at 14:02

You cannot use CASE expressions this way. One approach is to refactor your WHERE clause to not use CASE:

WHERE
    (@s1 = @s2 AND DatumBis BETWEEN '2019-01-01' AND '2019-12-31') OR
    (@s1 <> @s2 AND DatumBis BETWEEN '2018-12-30' AND '2019-01-05')

The predicate of a CASE expression, namely what follows either THEN or ELSE, has to be a literal value, not a logical expression.

How to use case statement by comparing two string inside where clause in sql

1 Answers1