Where Statement - Case Statement

Question

Post reply

Where Statement - Case Statement

Informer30

SSC-Addicted

Points: 436
More actions
August 13, 2015 at 5:16 am

#299765

Hi All,
I require the following case statement in the where clause so when it’s Monday we retrieve Thursday, Friday and Saturday data else use second condition as below.
(CASE WHEN DATEPART (DW,GETDATE()) = '2' THEN (table1.column1 - 4) ELSE table1.column1 END)
Issue the when parsing the case statement is returning the following error :
Msg 4145, Level 15, State 1, Line 25
An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Please can you advise what I am doing wrong here…
Many Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Let's get this clear.

If today is Monday, then you want to return data for Thursday, Friday and Saturday.

If today is Tuesday, Wednesday, Thursday or Friday then you want data for the previous day.

Is this correct?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Informer30 SSC-Addicted Points: 436 More actions · Answer 2

Informer30

SSC-Addicted

Points: 436

August 13, 2015 at 5:38 am

#1819618

Correct...Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Something like this should work

table1.column1 - (CASE WHEN DATEPART (DW,GETDATE()) = 2 THEN 4 ELSE 0 END)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

-- Make some data to play with

DROP TABLE #table1

CREATE TABLE #table1 (column1 DATETIME)

INSERT INTO #table1 VALUES (GETDATE()-1),(GETDATE()-2),(GETDATE()-3),(GETDATE()-4),(GETDATE()-5),(GETDATE()-6),(GETDATE()-7),(GETDATE()-8)

/* Take a peek

SELECT column1, DATENAME(weekday,column1)

FROM #table1

*/

GO

-- Pretend it's monday

DECLARE @getdate-2 DATETIME = GETDATE()-3

DECLARE

@RangeStart DATE = CASE WHEN DATENAME(weekday,@GETDATE) = 'monday' THEN @GETDATE-4 ELSE @GETDATE-1 END,

@RangeEnd DATE = @getdate-2

SELECT Today = @getdate-2, [DayOfWeek] = DATENAME(weekday,@GETDATE), RangeStart = @RangeStart, RangeStartDay = DATENAME(weekday,@RangeStart), RangeEnd = @RangeEnd, RangeEndDay = DATENAME(weekday,@RangeEnd)

SELECT column1, DATENAME(weekday,column1)

FROM #table1

WHERE column1 >= @RangeStart AND column1 <= @RangeEnd

GO

-- Pretend it's thursday

DECLARE @getdate-2 DATETIME = GETDATE()

DECLARE

@RangeStart DATE = CASE WHEN DATENAME(weekday,@GETDATE) = 'monday' THEN @GETDATE-4 ELSE @GETDATE-1 END,

@RangeEnd DATE = @getdate-2

SELECT Today = @getdate-2, [DayOfWeek] = DATENAME(weekday,@GETDATE), RangeStart = @RangeStart, RangeStartDay = DATENAME(weekday,@RangeStart), RangeEnd = @RangeEnd, RangeEndDay = DATENAME(weekday,@RangeEnd)

SELECT column1, DATENAME(weekday,column1)

FROM #table1

WHERE column1 >= @RangeStart AND column1 <= @RangeEnd

GO

-- So your actual code should look like this:

DECLARE

@RangeStart DATE = CASE WHEN DATENAME(weekday,GETDATE()) = 'monday' THEN GETDATE()-4 ELSE GETDATE()-1 END,

@RangeEnd DATE = GETDATE()

SELECT column1, DATENAME(weekday,column1)

FROM #table1

WHERE column1 >= @RangeStart AND column1 <= @RangeEnd

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Informer30 SSC-Addicted Points: 436 More actions · Answer 5

Thanks for code, however I am getting the following error message :

Msg 4145, Level 15, State 1, Line 28

An expression of non-boolean type specified in a context where a condition is expected, near ')'.

Informer30 SSC-Addicted Points: 436 More actions · Answer 6

Thanks Chris - excellent code....really appreciate the quick turnaround....I was looking for a variable free way....sorry...?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Informer30 (8/13/2015)
Thanks Chris - excellent code....really appreciate the quick turnaround....I was looking for a variable free way....sorry...?

Sorry, I didn't realise how new you are to SQL.

SELECT column1, DATENAME(weekday,column1)

FROM #table1

WHERE

column1 >= CAST(CASE WHEN DATENAME(weekday,GETDATE()) = 'monday' THEN GETDATE()-4 ELSE GETDATE()-1 END AS DATE)

AND column1 <= CAST(GETDATE() AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Informer30 SSC-Addicted Points: 436 More actions · Answer 8

That brilliant Chris...That has worked....

Many Thanks....

Ps. still intrigued why the case statement did not work....

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Informer30 (8/13/2015)
That brilliant Chris...That has worked....
Many Thanks....
Ps. still intrigued why the case statement did not work....

Can't tell from your code, but probably because you weren't comparing your expression to anything:

(CASE WHEN DATEPART (DW,GETDATE()) = '2' THEN (table1.column1 - 4) ELSE table1.column1 END) < CAST(GETDATE() AS DATE)

but this wouldn't be SARGable anyway since your columns are wrapped up in a CASE expression. (Google SARGable)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Informer30 SSC-Addicted Points: 436 More actions · Answer 10

Thanks again Chris....building the knowledge....

Many Thanks

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Informer30 (8/13/2015)
Thanks again Chris....building the knowledge....
Many Thanks

Any time, thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden