SQL date range Year-to-Date as of prior month end and previous year

Question

Have a query where the date ranges need to be year to date as of previous month's end and the respective date range of the previous year. I had a 'formula' that defined the date ranges by the numerical value of the month and year. e.g. previous month's end was current month minus one. This worked great until I hit January and being the first of the year and the I couldn't use the current year.

I am not an experienced SQL user and have used google to learn most of what I know, but have not had success finding the answer to this problem -found a formula for prior month's end, but not the remaining three dates below.

DECLARE @PYStart datetime = '1/1/2015'
DECLARE @PYEnd datetime = '12/31/2015' 
DECLARE @CYStart datetime = '1/1/2016' -- first day of previous month's year
DECLARE @CYEnd datetime = CONVERT(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),101) -- last day of past month

SELECT YEAR(transaction_date) as 'Year', MONTH(transaction_date) as 'Month'

FROM TABLE_Q1

WHERE((transaction_date BETWEEN @pystart and @pyend) OR (transaction_date BETWEEN @cystart AND @cyend))

group by year(transaction_date), month(transaction_date)

-Output below; Example output if you were to run today

Show us sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Jan 16 '17 at 18:57
Year-to-date. For this example, it would be year to date as of last month's end. E.g. as of today, it would be 1/1/2016 to 12/31/2016, but say in March of this year it would be 1/1/2017 - 2/28/2017 — James Davis, Jan 16 '17 at 18:58
Sorry not familiar with that term. Can you explain the logic? Looks weird you having problem to find the previous month. There are several examples for that . http://stackoverflow.com/questions/1424999/get-the-records-of-last-month-in-sql-server — Juan Carlos Oropeza, Jan 16 '17 at 18:59
Found a formula for prior month's end, but not the remaining three dates below - Finding answer to prior month's end on SO was not difficult, it is the other three dates that I am having troubles with. Prior to posting, I searched SO and other resources extensively and read the how to ask page - Tried my best to explain how I define the date ranges, but like I am not an experienced sql user and probably not familiar with the proper lingo - more than happy to expand on anything that may be confusing. Thank you for your time and help — James Davis, Jan 16 '17 at 19:18
"last month's end" appears to be the final day of the previous month. So, the steps would be: 1) Get previous month's end date, 2) Get the first day of the year containing the previous month's end date, 3) get the same information for the year prior to the year used above, 4) Handle leap year — Laughing Vergil, Jan 16 '17 at 19:22
You dont need to know the sql "lingo" to explain the problem. You can explain the logic talking about months and years, what happen on each case and what result you want. But you have to explain it like we dont know what are you talking about. That is when [**rubber duck**](https://en.wikipedia.org/wiki/Rubber_duck_debugging) can help — Juan Carlos Oropeza, Jan 17 '17 at 15:54
Juan, it does not appear that you read my entire question, looked at my code or glanced at any of the examples that I gave ('Example output if you were to run today'). Aside from the fact that 'year-to-date' is the most common date range in any form of financial or production reporting, if the term is not self-explanatory enough (or my title 'Year-to-date as of last month's end'), I provided the example starting and ending dates that the formulas are supposed to replace, commentary on what the date represents beside the date, attached a picture of the example output (you requested) — James Davis, Jan 18 '17 at 19:42
and took the time to expand on your previous comments giving example date ranges if we were currently in a different month. That is fine if you don't understand what year to date means and I get that I should have explained that 'year to date as of last month's end' means the start of the year up until the end of the previous month (come on man..), but if you have no intention on actually answering the question (or doing a quick google search) please do not waste both of our time.trolling.. BTW ('by the way'), does not look like everyone was confused... — James Davis, Jan 18 '17 at 19:55

score 0 · Answer 1 · answered Jan 16 '17 at 20:05

This is actually fairly straight forward, although the best method can depend on whether or not the field you will be selecting the date from range has times included. This answer handles both cases.

DECLARE @CYEnd datetime = CONVERT(varchar, DATEADD(day, -(DATEPART(day, GETDATE()) - 1), 101) -- First day of Current month
DECLARE @CYStart datetime = Cast(Cast(DATEPART(year, DATEADD(day, -1, @CYEnd)) as varchar(4)) + '0101' AS date)
DECLARE @PYEnd datetime = DATEADD(year, -1, @CYEnd)
DECLARE @PYStart datetime = dateadd(year, -1, @CYStart)

SELECT YEAR(transaction_date) as 'Year', 
    MONTH(transaction_date) as 'Month'
FROM TABLE_Q1

WHERE 
    (transaction_date >= @pystart and transaction_date < @pyend) 
    OR 
    (transaction_date >= @cystart AND transaction_date < @cyend)
group by 
    year(transaction_date), 
    month(transaction_date)

How it works Let us assume the current date is March 10th, 2016. In this case, the value in @CYEnd would be 2016-03-01, and @PYEnd would be 2015-03-01. With or without a time attached to the date, the checks (transaction_date >= @pystart and transaction_date < @pyend) and (transaction_date >= @cystart AND transaction_date < @cyend) will correctly return the records desired. A BETWEEN test using 2016-02-29 as @CYEnd will miss all of the dates that include times, although it will return a correct answer if no times are recorded as part of the field being filtered.

This also handles leap years elegantly, since 02/29 or 02/28 are both < 03/01.

Thank you so much for taking the time to help me out! The How it works was also very helpful and the logic was clear and makes a lot of sense. Will definitely use in other date ranges. When I ran the query, it resulted in an error, which I traced to the current year end (@CYEnd). I replaced your formula for @CYEnd with CONVERT(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),101) and it worked beautifully. I'm not completely sure why it did not work (may be something on my end), but it is running smoothly now and appreciate your help! This was a great learning experience! — James Davis, Jan 18 '17 at 19:58

SQL date range Year-to-Date as of prior month end and previous year

1 Answers1