May 23, 2012 at 12:29 pm
Hello Everyone!
I need to work out a SQL statement that does several things: take a given date, add 4 years to that date, then compare that to the current date to find out how many months out of date the +4years date is. Make sense?
So far I have:
SELECT Locality, Region, ADOPTION, DATEADD(year, 4, ADOPTION) AS EOPDUE, DATEDIFF(month, ADOPTION, EOPDUE) AS MonthsPastDue
FROM EOP
WHERE (DATEDIFF(month, ADOPTION, EOPDUE) > 48)
Which if give an ADOPTION date of 1/1/2007 returns 1/1/2011 (which is correct) now I need to take the 1/1/2011, compare it to today's date to hopefully return a value of 16. I think/know I need to use the GETDATE() function...just not really sure how to combine it all together to come out right.
so, based on the current query the return is:
Joe'sTown 6 1/1/2007 1/1/2011 60
What I need is:
Joe's Town 6 1/1/2007 1/1/2011 16
Any thoughts, words of wisdom, pointers will be GREATLY APPRECIATED!!!
Karen
May 23, 2012 at 12:36 pm
This is what I came up with, but I think there are some other issues with the query.
SELECT
Locality,
Region,
ADOPTION,
DATEADD(year, 4, ADOPTION) AS EOPDUE,
DATEDIFF(month, DATEADD(year, 4, ADOPTION), GETDATE()) AS MonthsPastDue
FROM
EOP
WHERE
(DATEDIFF(month, ADOPTION, EOPDUE) > 48) -- < this needs to be changed, what is the filter criteria?
May 23, 2012 at 12:40 pm
SELECT datediff(mm, dateadd(mm, 48, ADOPTION), getdate()) AS MonthsPastDue
FROM EOP
_________________________________
seth delconte
http://sqlkeys.com
May 23, 2012 at 12:58 pm
That works pretty darn good!
The "48" is 4 years in months. When I ran the query it did return "16" as, I thought it would. It did return more items then it should, to include some future dated items, so I will have to resolve that.
If it helps, this is tracking docuements that expire every 4 years, but the adoption dates very by year, and I am trying to capture what is out of date and how long it has been out of date.
You have been a gret help, Thank YOU!
May 23, 2012 at 12:59 pm
Thank YOU! this works well too! Adding it to my Query Notebook 🙂
May 23, 2012 at 1:01 pm
The problem with the WHERE clause is that it is forcing a table scan. SQL Server has to comput the difference betwen the two dates for every row in the table to determine if the rows should be included in the result set.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply