Solved

# Better way to write this case statement?

Posted on 2013-09-23
Medium Priority
257 Views
Is there a cleaner way to do this:
---------------------
set @mindate = '1/1/1950'

case when loandate > @mindate and payoutdate > @mindate and
datediff(day, c.LoanDate, c.PayOutDate) > 0 and
datediff(day, c.LoanDate, c.PayOutDate) < 31 then '30'

when loandate > @mindate and payoutdate > @mindate and
datediff(day, c.LoanDate, c.PayOutDate) >30 and
datediff(day, c.LoanDate, c.PayOutDate) < 61  then '60'

when loandate > @mindate and payoutdate > @mindate and
datediff(day, c.LoanDate, c.PayOutDate) > 0 and
datediff(day, c.LoanDate, c.PayOutDate) > 60 and
datediff(day, c.LoanDate, c.PayOutDate) <91  then '90'

when loandate > @mindate and payoutdate > @mindate and
datediff(day, c.LoanDate, c.PayOutDate) > 0 and
datediff(day, c.LoanDate, c.PayOutDate)  > 90 then '91'
end as AgeOfLoanBucket,
---------------------
I also have:
---------------------
case when c.Payoutdate > @mindate and c.Loandate > @mindate and
PayoutDate > LoanDate then
left(convert(varchar, Payoutdate, 120), 10) end as PayoutDate,

case when c.LoanDate > @mindate then
left(convert(varchar, c.loanDate, 120), 10) end as LoanDate,

case when c.LoanDate > @mindate and c.PayoutDate > @mindate and
datediff(day, c.LoanDate, c.PayOutDate) > 0 then
datediff(day, c.LoanDate, c.PayOutDate) end as Ageofloan,
0
Question by:Delta7428
• 3
• 2

LVL 66

Accepted Solution

Jim Horn earned 1200 total points
ID: 39514706
Since --> when c.Payoutdate > @mindate and c.Loandate > @mindate <-- is in all statements, it might be easier to read to do a nested CASE block.
``````SELECT CASE
WHEN loandate > @mindate and payoutdate > @mindate THEN
CASE WHEN datediff(day, c.LoanDate, c.PayOutDate) > 0 and  datediff(day, c.LoanDate, c.PayOutDate) < 31 then '30'
WHEN datediff(day, c.LoanDate, c.PayOutDate) >30 and datediff(day, c.LoanDate, c.PayOutDate) < 61  then '60'
WHEN datediff(day, c.LoanDate, c.PayOutDate) > 0 and datediff(day, c.LoanDate, c.PayOutDate) > 60 and datediff(day, c.LoanDate, c.PayOutDate) <91  then '90'
WHEN datediff(day, c.LoanDate, c.PayOutDate) > 0 and datediff(day, c.LoanDate, c.PayOutDate)  > 90 then '91' end
ELSE  -- insert something here to handle all other instances
END as AgeOfLoanBucket,
``````

Beyond that, none comes to mind, assuming you need all of those expressions to deterime the AgeOfLoanBucket value.  It might not be a bad idea to build a block that sets a value in all cases, then when you test you can make sure you've handled every scenario correctly.

btw in lines 5 and 6 the >0 expression seems redundant.
0

LVL 49

Assisted Solution

PortletPaul earned 800 total points
ID: 39514868
This is a :longer shot:

CROSS APPLY can be handy dandy for simplifying things. Look at the number of times datediff is called on in your original case expression (9).

select
....
from ....
cross apply ( select datediff(day, c.LoanDate, c.PayOutDate)  ) as ca1 ( LoanPayDiff )

The cross apply now supplies a name for that "LoanPayDiff" which can now be used just like a field.

Inserting this into Jim's re-working of the case expression:
``````SELECT CASE
WHEN loandate > @mindate and payoutdate > @mindate THEN
CASE WHEN ca1.LoanPayDiff > 0 and ca1.LoanPayDiff < 31 then '30'
WHEN ca1.LoanPayDiff >30 and ca1.LoanPayDiff < 61 then '60'
WHEN ca1.LoanPayDiff > 0 and ca1.LoanPayDiff > 60 and ca1.LoanPayDiff <91  then '90'
WHEN ca1.LoanPayDiff > 0 and ca1.LoanPayDiff > 90 then '91'
END
ELSE  -- insert something here to handle all other instances
END as AgeOfLoanBucket,
....

from ....
cross apply (
select datediff(day, c.LoanDate, c.PayOutDate)
) as ca1 ( LoanPayDiff )

``````
0

Author Comment

ID: 39514994
Paul, I get "Incorrect syntax near the keyword 'from'" on yours.  But I don't  think I want to use the cross apply. It just seems to add unnecessary clutter and take away from the readability.

I'll go with Jim's nested case.  For some reason I couldn't get it to work for me previously.

Can I put the datediff function in a string variable?  Why doesn't this work?  I get "incorrect syntax near keyword 'As' "

``````declare @loanDateDif varchar(30)
set @loanDateDif = 'datediff(day, c.LoanDate, c.PayOutDate)'

SELECT CASE
WHEN loandate > @mindate and payoutdate > @mindate THEN
CASE WHEN @loanDateDif > 0 and cast(@LoanDateDif, AS INT) < 31 then '30'
WHEN @loanDateDif >30 and cast(@LoanDateDif, AS INT) < 61  then '60'
WHEN @loanDateDif > 0 and cast(@LoanDateDif, as INT) > 60 and @loanDateDif <91  then '90'
WHEN @loanDateDif > 0 and cast(@LoanDateDif, as INT) > 90 then '91' end
ELSE 0
END as AgeOfLoanBucket,
``````
0

LVL 49

Expert Comment

ID: 39515027
>>It just seems to add unnecessary clutter
LOL, that's the opposite of my opinion, but also the cross apply reduces number of function executions. Oh well. As I said it was a longer shot, and Jim's stands-up by itself.

"Incorrect syntax near the keyword 'from'"
I should say that I wasn't proposing the complete query, there were bits you had to supply.

Thanks for trying it though.

>>Can I put the datediff function in a string variable?  Why doesn't this work?  I get "incorrect syntax near keyword 'As' "
NO!

The datediff() NEEDS to be calculated on a record by record basis, it cannot be treated as a constant.
0

LVL 49

Expert Comment

ID: 39515037
btw: if you were to provide the complete query (or all of the FROM clause with all joins) I could propose how to add the cross apply.
0

Author Comment

ID: 39515052
I got the cross apply to work.  I realized after I posted that I didn't work it into my query correctly.

And also that trying to treat the datediff function as a constant was a hair brain idea. :D
I was thinking in terms of putting part of a query string in a constant and lost sight of the function.

Thanks!  I love you guys!

Points coming.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.