troubleshooting Question

CASE in WHERE statement

Avatar of Mark Wilson
Mark Wilson asked on
Microsoft SQL Server
6 Comments1 Solution361 ViewsLast Modified:
I have developed the following query below


DECLARE @start DATETIME
DECLARE @end DATETIME
DECLARE @team varchar(25)

SET @start = DATEADD(day, 1 - DATEPART(day, GETDATE()),
                     CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120))
SET @start = DATEADD(month, -1, @start)
SET @end = DATEADD(day, -1, DATEADD(month, 1, @start)) + 1

SELECT  etc
FROM    etc
WHERE   CASE WHEN I.kbxreference LIKE 'LP%' THEN 'Law Pack'
             WHEN I.kbxreference LIKE 'TG%' THEN 'Telegraph'
             WHEN I.kbxreference LIKE 'JL%' THEN 'John Lewis'
             WHEN I.kbxreference LIKE 'IM%' THEN 'IM Wills Online'
             ELSE ws.name
        END = @team
        AND   CASE WHEN ws.worksourceid IN (211,212) THEN a.engrossed >= @start
        and a.engrossed < @end else m.created > @start and m.created < @end end

I am having problems with the following

 AND   CASE WHEN ws.worksourceid IN (211,212) THEN a.engrossed >= @start
        and a.engrossed < @end else m.created > @start and m.created < @end end

Is there any other way I can rewrite it to make it work?

Thanks
ASKER CERTIFIED SOLUTION
G Trurab Khan
Snr. Development Manager

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros