By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE Table1
("Individual_ID" varchar(1), "Treatment" varchar(1), "Date_Treatment_Received" timestamp, "Time_Between_Treatment(Month)" varchar(2))
;
INSERT INTO Table1
("Individual_ID", "Treatment", "Date_Treatment_Received", "Time_Between_Treatment(Month)")
VALUES
('A', 'B', '2010-01-01 01:00:00', '?'),
('A', 'B', '2010-01-02 01:00:00', '1'),
('A', 'B', '2010-01-04 01:00:00', '2'),
('A', 'B', '2011-01-05 01:00:00', '13'),
('A', 'B', '2011-01-06 01:00:00', '1'),
('A', 'B', '2011-01-08 01:00:00', '2'),
('A', 'B', '2012-01-09 01:00:00', '13'),
('A', 'B', '2012-01-10 01:00:00', '1'),
('B', 'C', '2010-01-01 01:00:00', '?'),
('B', 'C', '2010-01-02 01:00:00', '1'),
('B', 'C', '2010-01-04 01:00:00', '2'),
('B', 'C', '2011-01-05 01:00:00', '13'),
('B', 'C', '2011-01-06 01:00:00', '1'),
('B', 'C', '2011-01-08 01:00:00', '2'),
('B', 'C', '2012-01-09 01:00:00', '13'),
('B', 'C', '2012-01-10 01:00:00', '1')
;
CREATE TABLE
INSERT 0 16
WITH CTE AS (SELECT
"Individual_ID", "Treatment", "Date_Treatment_Received", "Time_Between_Treatment(Month)"
, CASE WHEN "Time_Between_Treatment(Month)" = '?' OR
"Time_Between_Treatment(Month)" :: int > 2 then 1 ELSE 0 END rnk
FROM Table1), CTE2 AS(
SELECT "Individual_ID", "Treatment", "Date_Treatment_Received", "Time_Between_Treatment(Month)",
SUM(rnk) OVER(PARTITION BY "Individual_ID", "Treatment" ORDER BY "Date_Treatment_Received") rnk
FROM CTE)
SELECT "Individual_ID", "Treatment", MIN("Date_Treatment_Received") as "Minimum Date", MAX("Date_Treatment_Received") aS "Maximum Date"
FROM CTE2
GROUP BY "Individual_ID", "Treatment", rnk
ORDER By "Individual_ID", "Treatment","Minimum Date"
| Individual_ID | Treatment | Minimum Date | Maximum Date |
|---|---|---|---|
| A | B | 2010-01-01 01:00:00 | 2010-01-04 01:00:00 |
| A | B | 2011-01-05 01:00:00 | 2011-01-08 01:00:00 |
| A | B | 2012-01-09 01:00:00 | 2012-01-10 01:00:00 |
| B | C | 2010-01-01 01:00:00 | 2010-01-04 01:00:00 |
| B | C | 2011-01-05 01:00:00 | 2011-01-08 01:00:00 |
| B | C | 2012-01-09 01:00:00 | 2012-01-10 01:00:00 |
SELECT 6