Optimize where clause using dates

Question

I'm trying to optimize several queries that all use a similar pattern on one of the WHERE clauses:

AND (DATEADD(DAY
            , ISNULL(a.[due_days], 30) + 30
            , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL))
            ) < GETDATE()

The CalcDate udf based on the type field value makes some comparisons and returns a date. Then adds an amount of days to that date and compares to current date. In order to be able to use an existing index on due_days I want to transform the operation to apply all the transformations to GETDATE(), let's say I want to make it sargable, if possible. Also, if there is some recommendation on what can be done to improve the use of the udf even better.

Answer 1

It's not so hard to do this transformation. Step by step:

DATEADD(DAY
       , ISNULL(a.[due_days], 30) + 30
       , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
       ) < GETDATE()

means:

[dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
  + (ISNULL(a.[due_days], 30) + 30) DAYS
< GETDATE()

then we have to break the ISNULL() into 2 cases:

    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (a.[due_days] + 30) DAYS
    < GETDATE()
OR
    a.[due_days] IS NULL
  AND 
    [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
      + (30 + 30) DAYS
    < GETDATE()

which can be written as:

    (a.[due_days] + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
OR
    a.[due_days] IS NULL
  AND 
    (30 + 30) DAYS
    < GETDATE() - [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)

so we can use DATEDIFF():

    (a.[due_days] + 30) 
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )
OR
    a.[due_days] IS NULL
  AND 
    (30 + 30) 
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              )

and finally:

    a.[due_days]  
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
OR
    a.[due_days] IS NULL
  AND 
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30

---

Corrected, taking care of the time parts:

    a.[due_days]  
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END
OR
    a.[due_days] IS NULL
  AND 
    30
    < DATEDIFF( day
              , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
              , GETDATE()
              ) - 30
      - CASE WHEN DATEADD( day
                         , DATEDIFF( day
                                   , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                                   , GETDATE()
                                   )
                         , [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
                         ) > GETDATE()
             THEN 1 ELSE 0
        END

You could simplify it a bit, with the use of CROSS APPLY:

CROSS APPLY
    ( SELECT gdt = GETDATE(),
             calc = [dbo].[CalcDate]([type], date1, date2, date3, date4, NULL)
    ) AS c
CROSS APPLY
    ( SELECT diff = x.diff - CASE WHEN DATEADD( day, x.diff, c.calc ) > c.gdt
                                 THEN 1 ELSE 0 
                             END
      FROM
          ( SELECT diff = DATEDIFF( day, c.calc, c.gdt) - 30
          ) AS x
    ) AS y    
----
    WHERE (  a.[due_days] < y.diff
         OR  a.[due_days] IS NULL  AND  30 < y.diff
          )