0

How to use case when to sort groups by age 0-18,18-30,30-40 using their EGN. Maybe using something like >87______, example of EGN 8607051914 this should be in the group 30-40.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ringanar R
  • 5
  • 3
  • 1
    What have you tried? Where didn't it work? – Dale K Apr 14 '20 at 08:43
  • Still trying to figure it out because the thing that came to me is to put it as Case when [EGN]>=7900000000 and [EGN] <=8900000000 then '30-40' but this targets only the year so a person may still not be 30y old in this format. And for the groups 18-30 the EGN for 18 is 01 for the year so i don't know exatcly how to put in a case when when the year for 30 is 89. – Ringanar R Apr 14 '20 at 08:50
  • 1
    Please, define EGN. Is this unique person id? – Zhorov Apr 14 '20 at 08:52
  • Start with calculating the current age. – jarlh Apr 14 '20 at 08:52
  • EGN is Unique person id, yes. – Ringanar R Apr 14 '20 at 08:52
  • How do i calculate the current age like this? – Ringanar R Apr 14 '20 at 08:53
  • Is the EGN `YYMMDDnnnn`, i.e. date of birth etc? – jarlh Apr 14 '20 at 08:53
  • Yeah its YYMMDDnnnn the 4 n's are randomised and don't need to be exact. Date of birth exactly. – Ringanar R Apr 14 '20 at 08:54
  • @RinganarR Is this a bulgarian EGN? – Zhorov Apr 14 '20 at 08:54
  • https://en.wikipedia.org/wiki/Unique_citizenship_number ? Someone must have written code to calculated current age. – jarlh Apr 14 '20 at 08:56
  • Yes it's for bulgarian type ID. – Ringanar R Apr 14 '20 at 08:56
  • To get the date first maybe we can use something that adds the number to make it a full year so for the year 99 to add 1900 to get the full year and sort it by that, but how can this be done in SQL Server, i found something for oracle : – Ringanar R Apr 14 '20 at 09:24
  • Select EGN, (CASE WHEN (Month_temp>20)AND(Month_temp<33) THEN Month_temp - 20 WHEN (Month_temp>40)AND(Month_temp<53) THEN Month_temp - 40 ELSE Month_temp END) as The_Month, (CASE WHEN (Month_temp>20)AND(Month_temp<33) THEN Year_temp + 1800 WHEN (Month_temp>40)AND(Month_temp<53) THEN Year_temp + 2000 ELSE Year_temp + 1900 END) as The_Year From ( Select EGN, TO_NUMBER(SUBSTR(EGN,1,2)) as Year_temp, TO_NUMBER(SUBSTR(EGN,3,2)) as Month_temp From TABLE_WITH_EGN_COLUMN ) – Ringanar R Apr 14 '20 at 09:24
  • [Edit] any clarifications etc directly into the question. – Dale K Apr 14 '20 at 09:52

1 Answers1

0

This is a specific calculation, because EGN seems to be a bulgarian citizen number. The important part for the calculation is that the initial six digits correspond to the birth date, but the month number is encoded:

  • for date of birth between 01/01/1900 and 31/12/1999 month remains the same
  • for date of birth before 01/01/1900 20 is added to the month,
  • for date of birth betwenn 01/01/2000 and 31/12/2099, 40 is added to the month:

The following statement is a possible approach:

Table:

CREATE TABLE Data (egn varchar(10))
INSERT INTO Data (egn) VALUES ('8607051914')

Table:

SELECT 
    egn,
    CASE 
        WHEN 0 <= age AND age < 18 THEN '0-18'
        WHEN 18 <= age AND age < 30 THEN '18-30'
        WHEN 30 <= age AND age < 40 THEN '30-40'
        ELSE '40+'
    END AS age
FROM (
    SELECT 
        egn,
        DATEDIFF(
            year,
            DATEFROMPARTS(
                CASE 
                    WHEN SUBSTRING(egn, 3, 2) BETWEEN '21' AND '32' THEN '18' + SUBSTRING(egn, 1, 2) 
                    WHEN SUBSTRING(egn, 3, 2) BETWEEN '41' AND '52' THEN '20' + SUBSTRING(egn, 1, 2) 
                    ELSE '19' + SUBSTRING(egn, 1, 2) END, 
                CASE 
                    WHEN SUBSTRING(egn, 3, 2) BETWEEN '21' AND '32' THEN SUBSTRING(egn, 3, 2) - 20
                    WHEN SUBSTRING(egn, 3, 2) BETWEEN '41' AND '52' THEN SUBSTRING(egn, 3, 2) - 40
                    ELSE SUBSTRING(egn, 3, 2) END, 
                SUBSTRING(egn, 5, 2)
            ),
            GETDATE()
        ) AS age
    FROM Data
) t

Result:

egn         age
8607051914  30-40
Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • It is working correctly, but how can it be integrated to use the table instead of a fixed input? – Ringanar R Apr 14 '20 at 10:00
  • @RinganarR it's almost the same, see the updated answer. – Zhorov Apr 14 '20 at 10:10
  • Just to note, `DATEDIFF(YEAR` doesn't actually work when determining ages. There's a good post here about this issue: https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate – Richard Hansell Apr 14 '20 at 10:45