Recently we ran into an issue where we were looking into why the data wasn't being returned on the first of the month for a particular web report. What we found was simple bad math, but what I learned was that sometimes efficiencies are found in what might appear more complex.
Below you will find the method used. and the problem is the subtracting of one (1) from the day of the month (
DAY(GETDATE())-1). On the first of the month, this would result in a ZERO (0) which is impossible and will not return a result. The same can be said for using this sort of math on the month when your month is January. Not a good plan.
/* ---------------------------------------------
This is the method in use...
---------------------------------------------
*/
SELECT DISTINCT [DateModified]
FROM [ITDS].[dbo].[Tickets] a
WHERE
YEAR(a.DateModified) = YEAR(GETDATE()) AND
MONTH(a.DateModified) = MONTH(GETDATE()) AND
DAY(a.DateModified) = DAY(GETDATE())-1
ORDER BY [DateModified] Desc
GO
Another method is to use
DATEDIFF() as a filter on the age (1=Yesterday). This is much heavier a load on the server and you might be better-off to avoid it.
/* ---------------------------------------------
This method is inefficient and will impact
performance more with large tables
---------------------------------------------
*/
SELECT DISTINCT [DateModified], DATEDIFF(dd,a.DateModified,GETDATE()) as Age
FROM [ITDS].[dbo].[Tickets] a
WHERE
DATEDIFF(dd,a.DateModified,GETDATE())=1
ORDER BY [DateModified] Desc
GO
Now, this combination of methods, using
YEAR(),
MONTH(), and
DAY(), with the proper calculation of
Yesterday is the charm. By setting the variable at the beginning, before the query, it is calculated once and the values for Year, Month, and Day are a simple comparison rather than the more complex effort that goes into
DATEDIFF() from our prior example.
/* ---------------------------------------------
This is the method adopted...
---------------------------------------------
*/
DECLARE @Yesterday as DateTime;
SET @Yesterday=DATEADD(dd,-1,GETDATE());
SELECT DISTINCT [DateModified]
FROM [ITDS].[dbo].[Tickets] a
WHERE
YEAR(a.DateModified) = YEAR(@Yesterday) AND
MONTH(a.DateModified) = MONTH(@Yesterday) AND
DAY(a.DateModified) = DAY(@Yesterday)
ORDER BY [DateModified] Desc
GO
Another advantage of this method is that if you want to filter on last month's data rather than Yesterday's it's
a few small changes.
/* ---------------------------------------------
This is the method adopted... Last Month
---------------------------------------------
*/
DECLARE @LastMonth as DateTime;
SET @LastMonth=DATEADD(mm,-1,GETDATE());
SELECT DISTINCT [DateModified]
FROM [ITDS].[dbo].[Tickets] a
WHERE
YEAR(a.DateModified) = YEAR(@LastMonth) AND
MONTH(a.DateModified) = MONTH(@LastMonth)
ORDER BY [DateModified] Desc
GO
The best scripters/programmers learn from their own mistakes as well as those others have made.
Addendum:
There's another way to get "
yesterday" that I saw, though I still believe the preferred method
might be the adopted method above, where the WHERE condition would read/include:
(a.DateModified >= DATEADD(dd,-1,GETDATE()) AND a.DateModified <= DATEADD(dd,-0,GETDATE()))
The problem I see in this methodology is that you would get the last 24-period, not "
yesterday" and the wastefulness of "
DATEADD(dd,-0,GETDATE())" in that it really gives you nothing more that
GETDATE() anyway. The result would give you, supposing that today might be August 10th, 2010 at 11:47:01 AM, August 9th, 2010 at 11:47:01AM through August 10th, 2010 at 11:47:01 AM. In effect the results could change through the day depending upon the time you ran the query. The adopted methodology would give you an absolute definition of yesterday, or last month, eliminating the time-of-day factor, which is preferred.
