Predicate not resulting in expected scan

So I have this query that I'm trying to tune and am running into an issue that I'm having trouble understanding. First of this is the query I'm using.

SELECT  
    si.LoanNbr AS [LoanNumber],
    fi.[SvcClientNbr] AS ClientID,
    si.LoanMasterID,
    si.LoanSrcCode AS [LoanSourceCode],
    fi.LoanPurpCode,
    fi.[PropState] AS [Property State],
    im.ImagedocumentID AS [Image Document ID],
    -- im.requestID AS [Request ID],
    CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
    im.[PageCount],
    im.[SignatureInd]
FROM dbo.NotMybaseTable Si
INNER JOIN dbo.NotMyTableName fi
    ON si.LoanMasterID = fi.LoanMasterID
INNER JOIN [dbo].[ImagedDocument] im
    ON si.loanmasterid = im.loanmasterid
    AND im.[DocTypeCode] = '10112'
WHERE CASE WHEN si.loansrccode = 'CORE' AND  Im.[SignatureInd] IN ('Y') THEN 1
        WHEN si.FundingSysCode = 'LIS' and CASE WHEN si.loansrccode = 'CORE' THEN 0 ELSE 1 END = 1 THEN 1
    ELSE 0 END = 1
    AND [ImageDate] BETWEEN DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, -30) AND DATEADD(WK, DATEDIFF(WK, 0, GETDATE()) - 4, 0) + 5

When I run this query the execution plan I get looks like this.

So the query is doing a very large scan on this large table returning 30 million rows just to pare it down to 3K. The index it's using looks like this.

CREATE NONCLUSTERED INDEX [IX_ImageDoc_DocType] ON [dbo].[ImageDoc]
(
    [LoanMasterID] ASC,
    [ImageDate] ASC,
    [ImageDocType] ASC
)

I altered to query in a way that gives it much better performance by moving the date portion of the predicate outside of the initial query like so.

```
select
*
from (
SELECT
si.LoanNbr AS [LoanNumber],
fi.[SvcClientNbr] AS ClientID,
si.LoanMasterID,
si.LoanSrcCode AS [LoanSourceCode],
fi.LoanPurpCode,
fi.[PropState] AS [Property State],
im.ImagedocumentID AS [Image Document ID],
CONVERT(VARCHAR(10),im.[ImageDate],101) AS ImageDate,
im.[PageCount],

Your query is looking for values between 2015-04-25 AND 2015-05-30.

Of which it looks like you have about 30 million.

Unfortunately there is a bug with the cardinality estimations where DATEDIFF is involved where the components get reversed.

SELECT DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, -30) ,  
       DATEADD(WK, DATEDIFF(WK, GETDATE(), 0) - 4, 0) + 5

Returns a range of 1784-05-15 to 1784-06-19. Extremely likely you have no dates in that range in the table (or statistics histogram) so SQL Server will estimate that no rows will be returned out of the scan, and certainly no need for 30 million lookups.

This is a fixed bug but requires trace flag 4199 enabled.

If that isn't possible you could try rephrasing the predicate in a different way that avoids the use of DATEDIFF or assigning the values to variables and using OPTION (RECOMPILE)

StackExchange Database Administrators Q#104890, answer score: 4