0

Using the prior month as the "end", I need a syntax for calculating/aggregating the number of days YTD and MTD. For example, if today is 3/6, then the YTD calculation should be for 1/1/2019 thru 2/28/2019. Similarly, the MTD calculation should be for 2/1/2019 thru 2/28/2019. If today is in January, MTD is Dec 1 - Dec 31, and YTD is Jan 1 - Dec 31.

2

Don't think about the end of a period, think about the beginning of the next period. You want everything on or after the beginning of the year (or the previous month), and before the beginning of the current month. This way you can write queries that aren't vulnerable to rounding issues when a data type of a column or parameter changes.

For YTD:

DECLARE @thismonth date = DATEADD(DAY, 1-DAY(GETDATE()), GETDATE());

...
WHERE col >= DATEFROMPARTS(YEAR(DATEADD(MONTH, -1, @thismonth)), 1, 1)
  AND col <  @thismonth;

For MTD:

DECLARE @thismonth date = DATEADD(DAY, 1-DAY(GETDATE()), GETDATE());

...
WHERE col >= DATEADD(MONTH, -1, @thismonth)
  AND col <  @thismonth;

If you just need a count, it's

-- YTD
SELECT DATEDIFF
(
  DAY,
  DATEFROMPARTS(YEAR(DATEADD(MONTH, -1, @thismonth)), 1, 1),
  @thismonth
); 

-- MTD
SELECT DATEDIFF
(
  DAY, 
  DATEADD(MONTH, -1, @thismonth),
  @thismonth
);

For some background on the rounding issues and why you don't want to be in the business of calculating the "end" of a period or use BETWEEN for date range queries:

  • That works perfect for coming up with the corresponding date parameters for MTD & YTD, but I'm also looking for the COUNT of Days for each. Example: MTD = 28 and YTD = 59 – Mike Jones Feb 28 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.