-3

I have a table that has the "document number" field, the type of flow (entering or leaving the country), date of birth, and the date the flow occurred.

Knowing that a person can leave or enter several times a year, how can I list those people (by their document number) of legal age (>=21yo) who were out of the country in a specific interval of the year (for example: between May 1, 2021 and August 1, 2021)?

ideally a syntax according to postgresql

EDIT: Info complement

Table: flows

id_flow doc_number brth_date flow_type flow_date
1 123 1999-05-01 in 2020-06-01
2 123 1999-05-01 out 2020-10-30
3 222 1988-01-09 out 2021-01-01
4 180 1993-11-15 out 2021-06-16
5 222 1988-01-09 in 2021-10-30
6 223 2009-09-05 out 2020-06-04
7 223 2009-09-05 in 2021-12-04

where dates are yyy-MM-DD and should return:

doc_number
123
222

because these people were out in the whole interval and has legal age.

0

1 Answer 1

-1

I think , you need sql statement like this:

select distinct person
from
(select person, max(date) as LEAVE_DATE from documentnumber where date<'2021-05-01' and flow='leaving' group by person ) A 
JOIN 
(select person,MAX(date) as ENTER_DATE from documentnumber where date<'2021-08-01' and flow='entering' group by person) B ON A.PERSON=B.PERSON 
where A.LEAVE_DATE>B.ENTER_DATE
1
  • 1
    Don't think this does what OP wants. It appears it's probably missing these things: "list those people ("by their document number)" and "of legal age (>=21yo)".
    – J.D.
    Commented Aug 31, 2023 at 12:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.