Show values within a range

Question

Show values within a range

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

1

I have the following SQL code:

SELECT (CAST(DATEDIFF(dd,Fecha_Nacimiento,GETDATE())/365.25 as int)) Edad 
FROM Clientes

Which gives me back the age, what I want to do is to be able to visualize in a query the age range, that is, a table with three columns representing the existing ranges. For example: if I have 15 records, five with an age between 18 and 27, another five with between 28 and 37 and another five with 38 and 48 years, I can visualize the number of records between the ranges that I said previously.

How could it be done?

sql sql-server

asked by Villatoro 11.09.2017 в 23:29

source

3 answers

1

If I understand correctly, you could use several queries through UNION, specifying the age range in each one.

Select count (*) where age ...

Union

Select count (*) Where age ...

answered by 11.09.2017 в 23:33

0

A form that occurs to me quickly would be using IF, but in this way I will show it in rows, I hope it will help you elaborate the solution.

SELECT nombre,
DATEDIFF(CURRENT_DATE, STR_TO_DATE(fecha_nacimiento, '%Y-%m-%d'))/365.25 AS edad,
IF(
(DATEDIFF(CURRENT_DATE, STR_TO_DATE(fecha_nacimiento, '%Y-%m-%d'))/365.25)  < 27, "de 0 a 27 Años",
IF(28 < (DATEDIFF(CURRENT_DATE, STR_TO_DATE(fecha_nacimiento, '%Y-%m-%d'))/365.25) < 36,"de 28 a 36 Años","Otros")) AS rangos
 FROM personas

answered by 12.09.2017 в 00:42

Control characters string c # I need to validate income of multiples of 100 by javascript, but it does not work

score 2 · Accepted Answer

If you use a DATEDIFF with year as a magnitude you get the difference in calendar years

DECLARE @Now DATETIME
SELECT  @Now = GETDATE() 

SELECT CASE WHEN DATEDIFF(year,Fecha_Nacimiento,@Now) < 18              THEN 'Menor a 18 Años',
            WHEN DATEDIFF(year,Fecha_Nacimiento,@Now) BETWEEN 18 AND 27 THEN 'de 18 a 27 Años',
            WHEN DATEDIFF(year,Fecha_Nacimiento,@Now) BETWEEN 28 AND 36 THEN 'de 28 a 37 Años',
            WHEN DATEDIFF(year,Fecha_Nacimiento,@Now) BETWEEN 28 AND 36 THEN 'de 38 a 46 Años',
            ELSE THEN 'Mayor de 46 Años'
       END
    FROM Clientes

Note: Particularly with GETDATE() because it is a non-deterministic function, that is, it does not always return the same result (obviously), and when using it several times in the same query, I prefer to work it from a variable ( @Now ) that will always keep the same value.