date within prior year range

Question

Post reply

date within prior year range

Tammy Robinson

Mr or Mrs. 500

Points: 567
More actions
September 18, 2007 at 2:46 pm

#95342

I am having a little trouble with a statement to retrieve a count of records that fall within a certain period.
I am trying to get the amount of records under 2 years, within 2 - 3 years, 4 - 5 years, 6 - 7 years, and 8 to 10 years, and over 10 years
For example, I am looking at records that would fall within 6 - 7 years prior to today's date. This looks at the year 2000 records.
I have the case statement:
Case When max(ub.Paint) between dateadd(yy,-7,getdate()) and dateadd(yy,-6,getdate()) Then 1 Else 0 End
I get some records for 2000 but not all.
How would I go about this?
Thanks in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

The way I did something like this was this way:

SELECTCOUNT(CASE WHEN YEAR(myDate) = 2000 THEN 1 ELSE NULL END) AS Count2000

Tammy Robinson Mr or Mrs. 500 Points: 567 More actions · Answer 2

Thanks for the reply, but 2000 was just my example because today's date is 9/18/2007. I don't want to hard code the years, I just want the statement to calculate the correct date for me. That way I won't have to change the report all the time.

Ray M SSC-Insane Points: 21093 More actions · Answer 3

select

Sum(case when datediff(yy, ub.Paint, getdate()) >= 2 then 1 else 0 end) as Last2,

Sum(case when datediff(yy, ub.Paint, getdate()) between 3 and 4 then 1 else 0 end) as [3-4],

From

mytable

But be careful of this solution. because datediff will show 1 year as long as the records were created in different years.

For example

This returns 1 even though it is only 1 day apart

select datediff(yy,'12/31/2007', '01/01/2008')

Result

1

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19000101', CURRENT_TIMESTAMP), '19000101') AS theDate UNION ALL

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19010101', CURRENT_TIMESTAMP), '19000101') UNION ALL

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19020101', CURRENT_TIMESTAMP), '19000101') UNION ALL

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19030101', CURRENT_TIMESTAMP), '19000101') UNION ALL

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19040101', CURRENT_TIMESTAMP), '19000101') UNION ALL

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19050101', CURRENT_TIMESTAMP), '19000101') UNION ALL

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19060101', CURRENT_TIMESTAMP), '19000101') UNION ALL

SELECT DATEADD(YEAR, DATEDIFF(YEAR, '19070101', CURRENT_TIMESTAMP), '19000101')

SELECT *

FROM Table1

WHERE Col1 >= DATEADD(YEAR, DATEDIFF(YEAR, '19030101', CURRENT_TIMESTAMP), '19000101')

AND Col1 < DATEADD(YEAR, DATEDIFF(YEAR, '19020101', CURRENT_TIMESTAMP), '19000101')

Also read this article

http://www.sqlteam.com/article/datediff-function-demystified

N 56°04'39.16"
E 12°55'05.25"

Tammy Robinson Mr or Mrs. 500 Points: 567 More actions · Answer 5

Tammy Robinson

Mr or Mrs. 500

Points: 567

September 19, 2007 at 6:44 am

#734556

RayM, Thanks for very much this worked fine.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

If you are concerned about speed, Ray's solution will not make use of any present index. Making a calculation with the indexed column renders the index useless.

N 56°04'39.16"
E 12°55'05.25"

Tammy Robinson Mr or Mrs. 500 Points: 567 More actions · Answer 7

Peter,

I have just ran my query. Speed is not an issue.

I am not sure what you are doing with your statement. Ray's was easier for me to understand.

Thanks for your advice.

Robert DeFazio Valued Member Points: 74 More actions · Answer 8

Why not just group the records according to the year of the date? The query would look somthing like this:

SELECT Year(dateField), Count(*) FROM tableName

GROUP BY Year(dateField)

This will return a count of records for each year represented in the data along with the year itself.

Robert DeFazio