The following answer was posted by Frank Kulash back in Oct of 2010 in response number 774064. His solution correctly brings back the correct personal ID and drug name. My question however is as follows. Can this be tweaked to bring back also the first fill date?
(
| | SELECT DISTINCT name |
| | , | drug_name |
| | , | MONTHS_BETWEEN ( TRUNC (SYSDATE, 'MONTH') |
| | , TRUNC (fill_date, 'MONTH') |
| | ) AS month_num |
| | FROM | table_x |
| -- | WHERE | ... | -- Any filtering goes here |
)
(
| | SELECT | name |
| | , | drug_name |
| | , | ROW_NUMBER () OVER ( PARTITION BY name |
| | , | drug_name |
| | ORDER BY | month_num |
| | ) - month_num | AS group_num |
| | FROM | got_month_num |
)
| SELECT DISTINCT name, | drug_name | |
| FROM | got_group_num |
| GROUP BY | name, | drug_name, group_num |
| HAVING | COUNT (*) | >= 3 |
| ORDER BY | name, | drug_name |
;