Case statement in where clouse with date compare

digvijay dobhal

Dear seniors, How I can use case statement in where clause….. Below query raised syntax error. @MyeffectiveDate Select * from Mytable where ((EffectiveDate <= @ MyeffectiveDate AND CASE WHEN BillingTerminationDate = NULL THEN (terminationDate is null or BillingTerminationDate > =@ MyeffectiveDate) ELSE BillingTerminationDate > =@ MyeffectiveDate) OR IsAdjustment=1) WHAT EXACLY I WANT If(BillingTerminationDate is null) then where clause should used ” (terminationDate is null or BillingTerminationDate > =@ MyeffectiveDate)” statement. Other wise used BillingTerminationDate > =@ MyeffectiveDate statement. Waiting for your need full reply. Thanks in advance.

Richard Deeming

digvijay dobhal wrote:

BillingTerminationDate = NULL

You can't use = to compare something to NULL; use Is Null instead.

digvijay dobhal wrote:

If BillingTerminationDate is null then ... BillingTerminationDate >= @MyeffectiveDate

If BillingTerminationDate is NULL, then it's not going to be greater than, less than, or equal to anything else.

digvijay dobhal wrote:

AND CASE WHEN BillingTerminationDate = NULL THEN (terminationDate is null or BillingTerminationDate >= @MyeffectiveDate) ELSE BillingTerminationDate >= @MyeffectiveDate)

You're missing an END on your CASE block. You also can't use a CASE block like this. What you want is probably something like this:

WHERE
(
(
EffectiveDate <= @MyeffectiveDate
And
(
(BillingTerminationDate Is Null And TerminationDate Is Null)
Or
BillingTerminationDate >= @MyeffectiveDate
)
)
Or
IsAdjustment = 1
)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Jorgen Andersson

CASE is an expression and not an executable statement. The general form of the predicate of a WHERE clause is expression operator expression where operator is = or <> or > or < etc. You can use a case as one of the expressions in the predicate. But you shouldn't need the case. Try this instead:

Select *
FROM Mytable
WHERE (
BillingTerminationDate is null
AND
terminationDate is null)
OR BillingTerminationDate > =@ MyeffectiveDate

and we'll see if I understood you correctly.

People say nothing is impossible, but I do nothing every day.

digvijay dobhal

Thanks Richard Deeming. I try above query bur not succeed. Finally I do increase number of query lines and doing below…..

select * from Mytable where ((EffectiveDate <= @MyeffectiveDate and BillingTerminationDate is not null
and (BillingTerminationDate > =@MyeffectiveDate)) OR IsAdjustment=1)

select * from Mytable where ((EffectiveDate <= @MyeffectiveDate and BillingTerminationDate is null
and (TerminationDate > =@MyeffectiveDate)) OR IsAdjustment=1)

Right now its working as my expactation. But having doubt that after reviewing, may or may not it will give me pain.:confused:

J4amieC

digvijay dobhal wrote:

Dear seniors,

Thats about the most offensive way ive seen to start a question.

Richard Deeming

OK, try:

WHERE
(
(
EffectiveDate <= @MyEffectiveDate
And
(
(BillingTerminationDate Is Not Null And BillingTerminationDate >= @MyEffectiveDate)
Or
(BillingTerminationDate Is Null And TerminationDate >= @MyEffectiveDate)
)
)
Or
IsAdjustment = 1
)

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

digvijay dobhal

Thanx.. its working :-D