Tell It To The Judge
A common dilemma is when you have two date columns, and you need to judge the gap between them for something.
For instance, say you have a table of orders and you want to figure out how long on average it takes an ordered item to ship, or a shipped item to be delivered.
You’re not left with many good ways to write the query to take advantage of indexes.
Let’s have a look-see.
We’re gonna skip the “aw shucks, this stinks without an index” part.
CREATE INDEX gloom ON dbo.Posts(CreationDate, LastActivityDate);
SELECT COUNT(*) AS records
FROM dbo.Posts AS p
WHERE DATEDIFF(YEAR, p.CreationDate, p.LastActivityDate) > 9;
Because it still stinks with an index. Check it out.
Though we have a predicate, and an index on both columns, we don’t have anything to seek to.
Why? Because our predicate isn’t on anything that the index is keeping track of.
Indexes don’t care how many years, months, days, hours, minutes, seconds, milliseconds, or microseconds difference there are between these two columns.
That’d be a really cool kind of index to have for sure, but insert a shrug that fills your screen the way dark matter fills the universe here.
All we got is workarounds.
Another For Instance
We can use a computed column:
ALTER TABLE dbo.Posts
ADD despair AS DATEDIFF(YEAR, CreationDate, LastActivityDate);
CREATE INDEX sadness ON dbo.Posts(despair);
The result is something we can seek to.
Which is probably the type of plan that you’d prefer.
Thanks for reading!