asked on 5/18/2011

CASE in WHERE statement

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

Microsoft SQL Server

Last Comment

cojdev

5/19/2011

EvilPostIt

5/18/2011

Does this do the trick?

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 
        else m.created  
        end between @start and @end

Open in new window

Alpesh Patel

5/18/2011

Select *,(Case when CategoryID In (1,2) then UnitPrice else UnitsInStock end ) from Products Where (Case when CategoryID In (1,2) then UnitPrice else UnitsInStock end ) > 20

Ephraim Wangoya

5/18/2011

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 (
(ws.worksourceid IN (211,212) and (a.engrossed >= @start and a.engrossed < @end))
or (m.created > @start and m.created < @end))

Mark Wilson

5/19/2011

ASKER

Thanks for the answer.

I have tried all of them, but none work correctly

If I choose at team they bring back all records relating to that team

ASKER CERTIFIED SOLUTION

G Trurab Khan

5/19/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

cojdev

5/19/2011

I am thinking the issue is in the data or the joins. Have you broken down the query?
How many records would you get with just the case statement?
How many records with just (ws.worksourceid IN (211,212) and (a.engrossed >= @start and a.engrossed < @end)?
How many records with (m.created > @start and m.created < @end)?
How many records with the Case Statement and (ws.worksourceid IN (211,212) and (a.engrossed >= @start and a.engrossed < @end)?
How many records with (m.created > @start and m.created < @end)?

How many records with the case and (m.created > @start and m.created < @end)?

Does this break down help show any flaws in the query or data?

good luck!