Group by Date of Hire for every 5 years

[Roni1]

How can I group in blocks of 5 years?
Like 1998-2002, 1993-1997, ...
Of course the group will change for next year e.g. 1999-2003, and so on.
CR8 SQL2000

Any help is appreciated.
Ron

 

[skuhlman]

select convert(integer,((year(getdate())-year(MyTable.MyDate))/5.0)) as FiveYearGroup,
count(*) as GroupCount
from MyTable
group by convert(integer,((year(getdate())-year(MyTable.MyDate))/5.0))
order by FiveYearGroup

 

[skuhlman]

Sorry for the post... for some reason I thought I was in the SQL group. However, you may be able to do something along the same lines withing Crystal

 

[skuhlman]

Sorry for the post... for some reason I thought I was in the SQL group. However, you may be able to do something along the same lines within Crystal.

 

[Roni1]

Thanks skuhlman!

Your SQL statement really helped me.

I created a formula @FiveYearGroup
fix(((year(today)) - year({MyTable.MyDate})) / 5)
in Crystal, and grouped on it.
I also created the formula below that covers up to 98 years:

select {@5 Year Group}

case 0: totext((year(today) - 4),0,'') + '-' + totext(year(today),0,'')
case 1: totext((year(today) - 9),0,'') + '-' + totext((year(today) - 5),0,'')
case 2: totext((year(today) - 14),0,'') + '-' + totext((year(today) - 10),0,'')
case 3: totext((year(today) - 19),0,'') + '-' + totext((year(today) - 15),0,'')
case 4: totext((year(today) - 24),0,'') + '-' + totext((year(today) - 20),0,'')
case 5: totext((year(today) - 29),0,'') + '-' + totext((year(today) - 25),0,'')
case 6: totext((year(today) - 34),0,'') + '-' + totext((year(today) - 30),0,'')
case 7: totext((year(today) - 39),0,'') + '-' + totext((year(today) - 35),0,'')
case 8: totext((year(today) - 44),0,'') + '-' + totext((year(today) - 40),0,'')
case 9: totext((year(today) - 49),0,'') + '-' + totext((year(today) - 45),0,'')
case 10: totext((year(today) - 54),0,'') + '-' + totext((year(today) - 50),0,'')
case 11: totext((year(today) - 59),0,'') + '-' + totext((year(today) - 55),0,'')
case 12: totext((year(today) - 64),0,'') + '-' + totext((year(today) - 60),0,'')
case 13: totext((year(today) - 69),0,'') + '-' + totext((year(today) - 65),0,'')
case 14: totext((year(today) - 74),0,'') + '-' + totext((year(today) - 70),0,'')
case 15: totext((year(today) - 79),0,'') + '-' + totext((year(today) - 75),0,'')
case 16: totext((year(today) - 84),0,'') + '-' + totext((year(today) - 80),0,'')
case 17: totext((year(today) - 89),0,'') + '-' + totext((year(today) - 85),0,'')
case 18: totext((year(today) - 94),0,'') + '-' + totext((year(today) - 90),0,'')
case 19: totext((year(today) - 98),0,'') + '-' + totext((year(today) - 95),0,'')