Hola RODRIGOMEJIA,
Podrías hacer lo siguiente:
SELECT
CASE
WHEN DATEDIFF(DAY, fechaNac , GETDATE()) BETWEEN 0 AND 27 THEN 'RECIÉN NACIDO'
WHEN DATEDIFF(DAY, fechaNac , GETDATE()) >= 28 AND DATEDIFF(MONTH, fechaNac , GETDATE()) <= 24 THEN 'LACTANTES'
WHEN DATEDIFF(YEAR, fechaNac , GETDATE()) BETWEEN 2 AND 5 THEN 'PRE-ESCOLAR'
WHEN DATEDIFF(YEAR, fechaNac , GETDATE()) BETWEEN 6 AND 12 THEN 'ESCOLAR'
...
END