SQL Query for YTD, MTD, WTD totalals

Question

I would like this request to be able to automatically find out the date and time of the day, as well as the first 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 that he will use GETDATE() in one form or another, but you do not want to see what I came up with, I promise!

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

I looked around here and Google - and did not find anything "clean" - so any input would be awesome!

Answer 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 the first day of the week in SQL Server, the default is Sunday if you use English English.

Answer 2

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

 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; 

Whatever you are, you can use in the request, for example. for YTD you would use:

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

Do not think that you need <part of the request if you are trying to get a counter right now. How many tickets will be checked tomorrow if I launched a request today? If you want to protect yourself from this, you can use:

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

You can make it a little more dynamic and pass it into a parameter that says “YTD”, “MTD” or “WTD”, for example

 CREATE PROCEDURE dbo.CountTickets @Range CHAR(3) = 'YTD' AS BEGIN SET NOCOUNT ON; -- you may want to handle invalid ranges, eg 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