February 1, 2012 at 8:45 am
I have a report subscription that returns the previous month's referrals for attorneys. I have most of it done but I am having some difficulty getting the Decmber with the previous year. I am using this to get the months Feb - Dec + year:
WHERE (MONTH(cases.date_received) = MONTH(GETDATE() - 1)) AND (YEAR(cases.date_received) = YEAR(GETDATE()))
cases.date_received is datetime
So when the the month is Jan the MONTH(GETDATE() - 1) = 0... What would be the best way to handle setting the value to 12 then doing year - 1?
Thanks in advance for your guidance.....
February 1, 2012 at 9:12 am
WHERE date_received >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND
date_received < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
February 1, 2012 at 9:14 am
A reasonably good way to do this is to calculate a start and end date for your range. Here's a little standard date arithmetic to give you some ideas:
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE())+1,-1) -- last day of this month (next month minus 1 day)
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),0) -- first day of this month
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE()),-1) -- last day of january 2012 (first day of this month minus 1 day)
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE())-1,0) -- first day of january 2012
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE())-1,-1) -- last day of december 2011 (first day of january 2012 minus 1 day)
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE())-2,0) -- first day of december 2011
SELECT DATEADD(month,DATEDIFF(month,0,GETDATE())-2,-1) -- last day of november 2011 (first day of december 2011 minus 1 day)
- you can perform the same arithmetic with year, hour, min etc.
Assuming your range is to be calculated within the query using GETDATE() as a reference, what would be the range start and end dates?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 1, 2012 at 9:16 am
Cadavre (2/1/2012)
WHERE date_received >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) ANDdate_received < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
Too quick for me today mate 😎
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
February 1, 2012 at 9:23 am
ChrisM@home (2/1/2012)
Cadavre (2/1/2012)
WHERE date_received >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) ANDdate_received < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
Too quick for me today mate 😎
I did type less than half of what you did, if I'd been slower it would've been a bit embarrassing 😀
February 1, 2012 at 9:26 am
Assuming your range is to be calculated within the query using GETDATE() as a reference, what would be the range start and end dates?
What I need to send is the previous month based on the date_received, the subscription would run on the first of the month for the previous month so today I would want the result set for Jan 2012, March 1, 2012 would return Feb 2012 and so on.... the part I am having difficulty with is on Jan 1, 2013 returning Dec 2012... and this would be from this year forward...
February 1, 2012 at 9:29 am
Paul Morris-1011726 (2/1/2012)
Assuming your range is to be calculated within the query using GETDATE() as a reference, what would be the range start and end dates?
What I need to send is the previous month based on the date_received, the subscription would run on the first of the month for the previous month so today I would want the result set for Jan 2012, March 1, 2012 would return Feb 2012 and so on.... the part I am having difficulty with is on Jan 1, 2013 returning Dec 2012... and this would be from this year forward...
As I said, do it like this: -
WHERE date_received >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) AND
date_received < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
It'll use any index you have on date_received which will make the query faster.
Basically, what the above is doing is confining the result set to where the date_received is greater than or equal to the 1st of the month before and less than the 1st of this month. That will automatically deal with your December issue because we're using the DATETIME data which already knows that if you minus 1 month from January then the result is December of the previous year.
February 1, 2012 at 9:32 am
groovy...
I apologize I am still a bit of a NOOB with this... I was trying to read up on what you provided to understand what it is doing... but you saved me some time 🙂
Thanks for your time!
February 1, 2012 at 9:39 am
Paul Morris-1011726 (2/1/2012)
groovy...I apologize I am still a bit of a NOOB with this... I was trying to read up on what you provided to understand what it is doing... but you saved me some time 🙂
Thanks for your time!
np, we all had to start at the beginning.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply