Better way to write this case statement?

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,
Delta7428Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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,

Open in new window


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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
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 )
 

Open in new window

0
Delta7428Author Commented:
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,

Open in new window

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

PortletPaulfreelancerCommented:
>>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
PortletPaulfreelancerCommented:
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
Delta7428Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.