SQL Query for YTD, MTD, WTD totals

Question

I would like this query to be able to automagically know today's date & time as well as the first of the year (or month) (or week)...

SELECT TicketID
FROM   Ticket
WHERE     (Ticket.DtCheckOut > '1/1/2011 12:00:00 AM') 
      AND (Ticket.DtCheckOut < '8/27/2011 12:00:00 AM')

I know it will use GETDATE() in some form, but you don't want to see what I've come up with, I promise!

Here is what I was reading on GETDATE() MDSN: GETDATE(Transact-SQL)

I looked around here and Google - and didn't find anything 'clean' - so any input would be awesome!

Solution 1

DECLARE @now DATETIME
SET @now = GETDATE()

SELECT
    DATEADD(yy, DATEDIFF(yy, 0, @now), 0) AS FirstDayOfYear,
    DATEADD(mm, DATEDIFF(mm, 0, @now), 0) AS FirstDayOfMonth,
    DATEADD(DAY, -DATEDIFF(dd, @@DATEFIRST - 1, @now) % 7, @now) AS FirstDayOfWeek

@@DATEFIRST is SQL Server's first day of the week, which defaults to Sunday if you are using U.S. English.

Solution 2

For the first day of the week it can be a bit tricky, depending on your actual requirements (whether you want to obey the user's datefirst setting or not, use Sunday regardless of the setting, etc.), see this question: Get first day of week in SQL Server. Here is one way to do it:

DECLARE
   @today DATE = CURRENT_TIMESTAMP,
   @y DATE,
   @m DATE,
   @w DATE;

SELECT
   @y = DATEADD(YEAR, DATEDIFF(YEAR, 0, @today), 0),
   @m = DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0),
   @w = DATEADD(DAY, 1-DATEPART(WEEKDAY, @today), @today);

SELECT
   [First day of year]  = @y,
   [First day of month] = @m,
   [First day of week]  = @w;

Whichever one you are after, you can use in the query, e.g. for YTD you would use:

SELECT TicketCount = COUNT(TicketID)
    FROM   dbo.Ticket
    WHERE  DtCheckOut >= @y;

Don't really think you need the < portion of the query if you're trying to get a count up to right now. How many tickets will have been checked out tomorrow if I'm running the query today? If you want to protect yourself against that you can use:

SELECT COUNT(TicketID)
    FROM   dbo.Ticket
    WHERE  DtCheckOut >= @y
    AND    DtCheckOut < DATEADD(DAY, 1, @now);

You could make it a little more dynamic and pass in a parameter that says 'YTD', 'MTD' or 'WTD', e.g.

CREATE PROCEDURE dbo.CountTickets
    @Range CHAR(3) = 'YTD' 
AS 
BEGIN
    SET NOCOUNT ON;

    -- you may want to handle invalid ranges, e.g.
    IF @Range NOT IN ('YTD', 'MTD', 'WTD')
    BEGIN
        RAISERROR('Please enter a valid range.', 11, 1);
        RETURN;
    END

    DECLARE
       @today DATE = CURRENT_TIMESTAMP,
       @start DATE;

    SELECT
       @start = CASE @range
          WHEN 'YTD' THEN DATEADD(YEAR,  DATEDIFF(YEAR,  0, @today), 0)
          WHEN 'MTD' THEN DATEADD(MONTH, DATEDIFF(MONTH, 0, @today), 0)
          WHEN 'WTD' THEN DATEADD(DAY, 1-DATEPART(WEEKDAY, @today), @today)
    END;

    SELECT 
        Range       = @range,
        TicketCount = COUNT(TicketID)
    FROM dbo.Ticket
    WHERE dtCheckOUt >= @start; 
END 
GO