select
sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 18)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
24)
)
then 1 else 0 end
) as [18-24]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 24)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
29)
)
then 1 else 0 end
) as [25-29]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 29)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
34)
)
then 1 else 0 end
) as [30-34]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 34)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
39)
)
then 1 else 0 end
) as [35-39]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 39)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
49)
)
then 1 else 0 end
) as [40-49]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) >=
50)
)
then 1 else 0 end
) as [50]
from tbluser
where isnumeric(ssno) = 1 and substring(ssno, 7,1) = 1
union all
select
sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 18)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
24)
)
then 1 else 0 end
) as [18-24]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 24)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
29)
)
then 1 else 0 end
) as [25-29]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 29)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
34)
)
then 1 else 0 end
) as [30-34]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 34)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
39)
)
then 1 else 0 end
) as [35-39]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) > 39)
AND
(year(getdate()) - cast('19'+ left(ssno,2) as int) <=
49)
)
then 1 else 0 end
) as [40-49]
,sum(
case when
(
(year(getdate()) - cast('19'+ left(ssno,2) as int) >=
50)
)
then 1 else 0 end
) as [50]
from tbluser
where isnumeric(ssno) = 1 and substring(ssno, 7,1) = 2
|