best way to call function (again and again)

Question

I have a large table (1 mio rows) and have this query:

Select ...,
 case
   when datediff(day,getdate())<30 then 'Month'
   when datediff(day,getdate())<90 then 'Quater"
   when datediff(day,getdate())<365 then 'YEAR'
   else  'OLD'  END
....

how do I prevent it from executing the function three times for each old row..??

I would love something like

case datediff(day,getdate())
when between 0 and 30 then 'month'
when between 31 and 90 then...

Answer 1

Are you sure that the datediff()-function will be repeated n times? The optimizer should be smart enough to see this afaik.

Besides that what you can always do is to calculate datediff for every row in a query and apply the case in an outer query.

select ..., case when a.my_diff ..., ... from (
    select ..., datediff(...) as 'my_diff', ... from ...
) a