How to use CASE in SELECT and WHERE clause to Eliminate divide by zero issue

Question

Using SQL Server 2008 R2, I have a query which is now giving me a divide by zero error. This is because of a DB column value which was always supposed to be > 0. This has been changed. The column is wf.reminderFrequency and is now set to no nulls with a default value of 0.

Here is the query :

SELECT 
    wfi.WebFormsInstanceID AS instanceID, 
    wfi.FormActionDate as fLastActionDate, 
    wf.reminderFrequency as fReminderFeq, 
    DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE),
    CAST(GETDATE() AS DATE)) AS theDateDiffCalc,
    DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE),
    CAST(GETDATE() AS DATE)) % wf.reminderFrequency AS theModCalc 
FROM 
    (webFormsInstances as wfi 
LEFT OUTER JOIN 
    WebFormsIndex as wf ON wfi.WebFormsIndexID = wf.WebFormsIndexID) 
WHERE 
    (wfi.formStage <> 'Complete' 
     AND wfi.FormStage <> 'Terminated' 
     AND wfi.FormStage <> 'Payroll Processing') 
    AND (wfi.FormActionDate > 20150101) 
    AND (DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE), CAST(GETDATE() AS DATE)) % wf.reminderFrequency = 0) 
ORDER BY 
    wfi.WebFormsInstanceID DESC;

The query is designed to get all the WebFormsIndex records where no action (difference between today and fLastActionDate) has happened in X (wf.reminderFrequency) number of days. Please note - The query is more confusing than it needs to be because fLastActionDate is an YYYYMMDD integer value which unfortunately can not be changed. So how do I change this to handle the scenario where wf.reminderFrequency can now be 0. As you can see this value is being used in a two divisions n the query.

This is my solution for the SELECT part which I think is going to work:

SELECT 
    wfi.WebFormsInstanceID AS instanceID, 
    wfi.WebFormsIndexID as fID, 
    wf.FormName as fName, 
    wfi.FormOwner as fOwner, 
    wfi.FormStage as fStage, 
    wfi.FormAction as fAction, 
    wfi.FormActionDate as fLastActionDate, 
    wf.reminderFrequency, 
    wfi.Comments as fComments, 
    DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE),
    CAST(GETDATE() AS DATE)) AS theDateDiffCalc,
    CASE
       WHEN wf.reminderFrequency > 0 
         THEN
           DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE), CAST(GETDATE() AS DATE)) % wf.reminderFrequency 
         ELSE
            '-1'
    END AS theModCalc
FROM 
    (webFormsInstances as wfi     

But I am having no luck figuring out how to handle the division in the WHERE clause.

This does not work:

WHERE 
    (wfi.formStage <> 'Complete' 
     AND wfi.FormStage <> 'Terminated' 
     AND wfi.FormStage <> 'Payroll Processing') 
    AND (wfi.FormActionDate > 20150101) 
    CASE
       WHEN wf.reminderFrequency > 0 
         THEN
               AND (DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE), CAST(GETDATE() AS DATE)) % wf.reminderFrequency = 0) 
        END AS          
ORDER BY 
    wfi.WebFormsInstanceID DESC;

UPDATE - Sample data from the first statement:

  instanceID  fLastActionDate  fReminderReq  theDateDiffCalc  theModCalc
  24965         20150312          14             14              0
  24965         20150312          14             14              0
  24940         20150226          14             28              0

Thanks to @Gordon using nullif was the solution. Seems to work very well. It was used like so in the select:

 DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE),CAST(GETDATE() AS DATE)) % nullif(wf.reminderFrequency,0) AS theModCalc 

and like so in the WHERE:

AND (DATEDIFF(day, CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE),CAST(GETDATE() AS DATE)) % nullif(wf.reminderFrequency,0) = 0) 

Answer 1

If you can handle NULL as the value instead of -1, I would recommend NULLIF():

DATEDIFF(day,
         CAST(CAST(wfi.formActionDate AS VARCHAR(8)) AS DATE),
         CAST(GETDATE() AS DATE)
        ) % nullif(wf.reminderFrequency, 0) AS theModCalc 

This will work in both the select and the where.