Sample Header Ad - 728x90

Count days between dates and find largest gap

0 votes
1 answer
291 views
I need to find all members in our database from a calendar month that do not have a gap of 10+ days between sending data / or in the month total i.e. everyone in April 2018. The table is set up like so (although much larger): +-----------+------------+ | member_id | data_date | +-----------+------------+ | 1 | 2018-04-10 | | 5 | 2018-04-16 | | 1 | 2018-04-11 | | 2 | 2018-04-12 | | 3 | 2018-04-13 | | 4 | 2018-04-12 | | 5 | 2018-04-15 | | 3 | 2018-04-19 | | 2 | 2018-04-17 | | 1 | 2018-04-18 | | 5 | 2018-04-10 | | 2 | 2018-04-18 | | 1 | 2018-04-08 | | 2 | 2018-04-03 | | 3 | 2018-04-02 | | 4 | 2018-04-14 | | 5 | 2018-04-15 | | 3 | 2018-04-16 | | 2 | 2018-04-19 | | 1 | 2018-04-14 | +-----------+------------+ (member_id,data_date) is defined UNIQUE. Each data_date represents one day that data was sent. There are duplicate data_dates for each member_id. I am running PostgreSQL 8.2.15. It is Greenplum. There are up to 30 data_dates for each member_id in the month, I am having trouble figuring out how to find the largest gap without data being sent in the entire month for each member. Here is an example of some test data: create temp table tempdata ( member_id integer NOT NULL, data_date date ); INSERT INTO tempdata(member_id, data_date) VALUES (1, '2017-04-01') , (1, '2017-04-02') , (1, '2017-04-03') , (1, '2017-04-04') , (1, '2017-04-05') , (1, '2017-04-06') , (1, '2017-04-07') , (1, '2017-04-08') , (1, '2017-04-09') , (1, '2017-04-10') , (1, '2017-04-11') , (1, '2017-04-12') , (1, '2017-04-13') , (1, '2017-04-14') , (1, '2017-04-15') , (1, '2017-04-16') , (1, '2017-04-17') , (1, '2017-04-18') , (1, '2017-04-19') , (1, '2017-04-20') , (1, '2017-04-21') , (1, '2017-04-22') , (1, '2017-04-23') , (1, '2017-04-24') , (1, '2017-04-25') , (1, '2017-04-26') , (1, '2017-04-27') , (1, '2017-04-28') , (1, '2017-04-29') , (1, '2017-04-30') , (2, '2017-04-09') , (2, '2017-04-10') , (2, '2017-04-11') , (2, '2017-04-12') , (3, '2017-04-01') , (3, '2017-04-02') , (3, '2017-04-03') , (3, '2017-04-04') , (3, '2017-04-05') , (3, '2017-04-06') , (3, '2017-04-07') , (3, '2017-04-08') , (3, '2017-04-09') , (3, '2017-04-10') , (3, '2017-04-11') , (3, '2017-04-12') , (3, '2017-04-13') , (3, '2017-04-14') , (3, '2017-04-15') , (3, '2017-04-16') , (3, '2017-04-17') , (3, '2017-04-18') , (3, '2017-04-19') , (3, '2017-04-20') , (3, '2017-04-21') , (3, '2017-04-22') , (3, '2017-04-23') , (3, '2017-04-24') , (3, '2017-04-25') , (3, '2017-04-26') , (3, '2017-04-27') , (3, '2017-04-28') , (3, '2017-04-29') , (3, '2017-04-30') , (4, '2017-04-01') , (4, '2017-04-02') , (4, '2017-04-03') , (4, '2017-04-04') , (4, '2017-04-05') , (4, '2017-04-06') , (4, '2017-04-07') , (4, '2017-04-08') , (4, '2017-04-09') , (4, '2017-04-10') , (4, '2017-04-11') , (4, '2017-04-12') , (4, '2017-04-13') , (4, '2017-04-14') , (4, '2017-04-15') , (4, '2017-04-16') , (4, '2017-04-17') , (4, '2017-04-18') , (4, '2017-04-19') , (4, '2017-04-20') , (4, '2017-04-21') , (4, '2017-04-22') , (5, '2017-04-01') , (5, '2017-04-02') , (5, '2017-04-03') , (5, '2017-04-04') , (5, '2017-04-05') , (5, '2017-04-06') , (5, '2017-04-07') , (5, '2017-04-08') , (5, '2017-04-09') , (5, '2017-04-10') , (5, '2017-04-11') , (5, '2017-04-12') , (5, '2017-04-13') , (5, '2017-04-14') , (5, '2017-04-15') , (5, '2017-04-16') , (5, '2017-04-17') , (5, '2017-04-18') , (5, '2017-04-22') , (5, '2017-04-23') , (5, '2017-04-24') , (5, '2017-04-25') , (5, '2017-04-26') , (5, '2017-04-27') , (5, '2017-04-29') , (5, '2017-04-30') , (6, '2017-04-01') , (6, '2017-04-02') , (6, '2017-04-03') , (6, '2017-04-04') , (6, '2017-04-05') , (6, '2017-04-06') , (6, '2017-04-07') , (6, '2017-04-08') , (6, '2017-04-09') , (6, '2017-04-10') , (7, '2017-04-01') , (7, '2017-04-04') , (7, '2017-04-05') , (7, '2017-04-06') , (7, '2017-04-07') , (7, '2017-04-08') , (7, '2017-04-09') , (7, '2017-04-11') , (7, '2017-04-12') , (7, '2017-04-13') , (7, '2017-04-14') , (7, '2017-04-15') , (7, '2017-04-16') , (7, '2017-04-17') , (7, '2017-04-18') , (7, '2017-04-19') , (7, '2017-04-21') , (7, '2017-04-22') , (7, '2017-04-26') , (7, '2017-04-27') , (7, '2017-04-28') , (7, '2017-04-30') , (8, '2017-04-02') , (8, '2017-04-03') , (8, '2017-04-04') , (8, '2017-04-05') ;
Asked by DataGwynn (13 rep)
Jun 25, 2018, 09:40 PM
Last activity: Jun 27, 2018, 08:45 PM