Cannot do SQL Case when bit value = 1 THEN post_date >= DATEADD(day, -1, GETDATE())-CodePudding

I have a large select query and need to enter a where statement when a bit var = 1 (and in most cases they won't so) then we need to make sure we compare the post_date var is NOT within the past 24hours.

What I am trying to do is:

case when bit value = 1 THEN post_date >= DATEADD(day, -1, GETDATE())

But for all other records that do not have that bit var set to 1, we get those records.

So here is what I was trying to do:

SELECT 
WARNING_EVENTS.id, 
WARNING_EVENTS.message_id, 
WARNING_EVENTS.warning, 
MESSAGE_PREPARED.county
FROM WARNING_EVENTS
INNER JOIN MESSAGE_PREPARED ON WARNING_EVENTS.message_id = MESSAGE_PREPARED.message_id

WHERE MESSAGE_PREPARED.county IS NOT NULL
AND MESSAGE_PREPARED.county <> ''
AND ACCOUNTS.noaa = 1
AND ACCOUNTS.active = 1
AND MESSAGE_PREPARED.active = 1
AND 
CASE repeat_prevention
         WHEN 1 THEN MESSAGE_PREPARED.last_post

And of course this was not working. I can do it with the code web server but know the best way to do it is in the query.

In the message_prepared table there are some record that we set a bit flag to 1 to denote that we should only include them if the post_date is outside of 24hrs.

Any help would be greatly appreciated.

Thanks so much!!

CodePudding user response：

You don't need a CASE expression for this, just simple AND/OR logic e.g.

AND (
    repeat_prevention = 0
    OR MESSAGE_PREPARED.last_post >= DATEADD(day, -1, GETDATE())
)

Note: CASE is an expression i.e. it allows you to conditionally return a value. Its not a statement which would allow you to conditionally add a further statement.