Count DIFFDATE Exceeding A Specified Value - Step 3

Question

Along the same vein as the questions asked yesterday, I have another "count if" dilemma. Now I need to pull a count of the condition entries that meet the following parameters:

1. ConditionLevelEntryID = 189
2. CheckoffDate is > 14 days from ConditionEntryDateTime
3. CheckoffDate IS NULL and ConditionEntryDateTime is > 14 days old

I have attempted the query (below) which doesn't work. The error returned is below the query. Please help me refine the query to get an accurate count. Thanks in advance.

select Count(*)
from conditionentrytable c
where conditionlevelentryid=189
    and 
    ((c.checkoffdate IS NULL 
    and 
    convert(varchar(12),DATEADD(day,14,c.conditionentrydatetime)))
    or 
    DATEDIFF(dd,c.checkoffdate,c.conditionentrydatetime)>14)

Server: Msg 170, Level 15, State 1, Line 7 Line 7: Incorrect syntax near ')'.

Answer 1

A little rusty myself but there's nothing being done with the converted conditionentrydatetime in the IS NULL expression.

How about?

select Count(*)
from conditionentrytable c
where conditionlevelentryid=189
    and 
    ((c.checkoffdate IS NULL 
    and 
    DATEDIFF(dd,getdate(),c.conditionentrydatetime)>14)
    or 
    DATEDIFF(dd,c.checkoffdate,c.conditionentrydatetime)>14))

Answer 2

Try this:

SELECT  COUNT(*)
FROM    conditionentrytable c
WHERE   conditionlevelentryid=189
        AND DATEDIFF(dd, COALESCE(c.checkoffdate, GETDATE()),c.conditionentrydatetime) > 14

, or even better:

SELECT  COUNT(*)
FROM    conditionentrytable c
WHERE   conditionlevelentryid=189
        AND c.conditionentrydatetime > COALESCE(c.checkoffdate, GETDATE()) - 14

The latter query will use a composite index on (conditionlevelentryid, conditionentrydatetime) if you have one, which will most probably greatly improve the query performance.