CODEWITHSUNDEEP
sqlsql-servercase
nichu09
nichu09

Reputation: 892

How to use the SQL CASE Statement in between conditions

i'm new to the SQL Case Statement.When I use case statement's 'between' clauses its giving an error. Query below

SELECT CASE
       WHEN @Divid=1 THEN CONVERT(varchar(20),datename(mm,m.dob)) + ' ' + CONVERT(varchar(20),datename(DAY ,m.dob))
       ELSE CONVERT(varchar(20),datename(mm,m.ActualDOB)) + ' ' + CONVERT(varchar(20),datename(DAY ,m.ActualDOB))
   END 'DOB' ,
       m.FirstName + ISNULL(m.LastName,'') 'Name',
                                           m.EmployeeNo ,
                                           m.Customer
FROM sEmployeeMaster m
JOIN lDivision d ON m.BizVertical=d.Division
WHERE d.DivisionID= @Divid
  AND CASE
      WHEN @Divid=1 THEN MONTH(dob) BETWEEN MONTH(getdate()) AND MONTH(getdate()+2)
           AND DAY(dob)BETWEEN DAY(getdate()) AND DAY(getdate()+2)
      ELSE MONTH(ISNULL(m.ActualDOB,dob) BETWEEN MONTH(getdate()) AND MONTH(getdate()+2)
           AND DAY(m.ActualDOB,
                   dob)BETWEEN DAY(getdate()) AND DAY(getdate()+2)
  END

Upvotes: 1

Views: 649

Answers (1)

D Stanley
D Stanley

Reputation: 152556

You can't use branching and conditional statements in where clauses. You just need to change it to a complete logical statement:

 WHERE d.DivisionID= @Divid
    AND (
        (@Divid = 1 AND
            MONTH(dob) BETWEEN MONTH(getdate()) AND  MONTH(getdate()+2)
            AND DAY(dob)BETWEEN DAY(getdate()) AND  DAY(getdate()+2)) 
        OR
        (@Divid <> 1 AND
            MONTH(ISNULL(m.ActualDOB,dob) BETWEEN MONTH(getdate()) AND  MONTH(getdate()+2)
            AND DAY(m.ActualDOB,dob)BETWEEN DAY(getdate()) AND  DAY(getdate()+2)) 
        )

Upvotes: 5

Related Questions