CODEWITHSUNDEEP
sql-servercasewhere-clause
SASUSMC
SASUSMC

Reputation: 681

Why use Case in Where Clause

I know there are variations of this question out there but cannot find one that answers what I am looking for.

I have inherited a database and reports from another programmer who is no longer in the picture.

One of the Queries uses this code:

Select
     b.HospitalMasterID
    ,b.TxnSite
    ,b.PatientID
    ,b.TxnDate as KeptDate
From
    Billing as b
    Inner Join Patient as p
        on b.HospitalMasterID = p.HospitalMasterID
        and b.PatientID = p.PatientID
Where
    b._IsServOrItem=1
    and b.TxnDate >= '20131001'
    and (Case
            When b.ExtendedAmount > 0 Then 1
            When (Not(p.PlanCode is null)) and (b.listAmount >0) then 1
        End = 1)

When I run the Query I get apx 900,000 rows returned. If I remove the Case statement, I get over a million rows returned.

Can someone explain why this is so? What exactly is the case statement doing? Is there a better way to accomplish the same thing. I really don't like this statement as it stands and the entire report query is very difficult to read due to lack of structure.

Version of Sql is T-Sql 2012.

Thanks,

Upvotes: 2

Views: 95

Answers (2)

Matt Whitfield
Matt Whitfield

Reputation: 6574

I think that's someone trying to avoid using the OR operator in order to promote index seeks over scans. It would be worth looking at the plan, but I would be surprised if it differed significantly over the logic in Greg's answer.

Upvotes: 1

Greg
Greg

Reputation: 16680

Seems to me like it's doing this:

(b.ExtendedAmount > 0 OR (Not(p.PlanCode is null) and (b.listAmount >0)))

Maybe it was copy / pasted from somewhere else and modified? Regardless, it's bizarre.

Upvotes: 5

Related Questions