PostgreSQL Data Selection

Question

Is it possible to write PostgreSQL code that looks at the sample data in the selects only the persons who have been active for the whole first quarter( 01/01/2018 to 03/31/2018) as shown in the desired output? Note that person H should not be selected because they are missing January.

Sample Data

    Person  Start Date  End Date
    A       1/1/2018    1/31/2018
    A       2/1/2018    2/28/2018
    A       3/1/2018    3/31/2018
    B       1/1/2018    2/28/2018
    C       1/1/2018    2/28/2018
    C       3/1/2018    3/31/2018
    D       2/1/2018    3/31/2018
    E       2/1/2018    2/28/2018
    F       1/1/2018    3/31/2018
    G       1/1/2018    4/30/2018
    H       2/1/2018    4/30/2018

Desired Output

    Person
    A
    C
    F
    G

Please edit the question and paste the example data and expected output as text, remove images. — klin
– klin, Commented Nov 8, 2019 at 17:47

score 2 · Accepted Answer · 2019-11-08 18:44:44Z

2

Assuming your columns are proper DATE columns and there are no overlaps, you could do something like this:

select person
from the_table
group by person
having sum(end_date - start_date + 1) >= date '2018-03-31' - date '2018-01-01' + 1
order by person;

Subtracting one date from another yields the number of days between those two dates. Then the sum of all differences is compared to the difference between the start and end date of the quarter.

Online example: https://rextester.com/OIN10602

edited Nov 8, 2019 at 18:44

answered Nov 8, 2019 at 18:37

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

GTA Over a year ago

Hi, I added a follow up question to this. Would you be able to take a look please?stackoverflow.com/questions/58805289/…

Collectives™ on Stack Overflow

PostgreSQL Data Selection

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related