CODEWITHSUNDEEP
postgresql
GTA
GTA

Reputation: 75

PostgreSQL Data Selection

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

Upvotes: 0

Views: 46

Answers (1)

user330315
user330315

Reputation:

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

Upvotes: 2

Related Questions