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

Aaron Ingold

SSCrazy Eights

Points: 9070

September 18, 2007 at 2:57 pm

#734415

The way I did something like this was this way:

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

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

Tammy Robinson

Mr or Mrs. 500

Points: 567

September 18, 2007 at 3:02 pm

#734417

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: 39725 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: 39725 More actions · Answer 6

SwePeso

SSC-Dedicated

Points: 39725

September 19, 2007 at 8:04 am

#734605

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"