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!

You want the totals for all three in the same query? Also what does first day of week mean to you? — Martin Smith, Aug 26 '11 at 23:09
Sorry - no three different query, similar goal that's all... First day of week (for this) is Sunday. — Fuginator, Aug 26 '11 at 23:10
Oh, and to be more clear, I want to COUNT() the TicketID column... — Fuginator, Aug 26 '11 at 23:11

NullUserException · Accepted Answer · 2011-08-26T23:24:09.597

4

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.

edited Aug 26 '11 at 23:24

answered Aug 26 '11 at 23:11

NullUserException

83,810
28
209
234

not sure why declaring a variable here when you can just put the getdate() where the @now is? DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0) AS FirstDayOfYear, – donviti Feb 04 '15 at 13:24

score 2 · Answer 2 · edited May 23 '17 at 11:53

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

SQL Query for YTD, MTD, WTD totals

2 Answers2