So I am finding all rows within a date range that are of the BLUE type.
SELECT * FROM table WHERE date_of_service BETWEEN CAST ('2020-01-01' AS DATE) AND CAST ('2020-09-30' AS DATE) AND type = 'BLUE'
Then I want to remove rows (based on ID) with a BLUE type that also have a row of another color with the same ID, dated within 90 days previous to the date of the BLUE type. If colors other than BLUE with the same ID are dated more than 90 days previous to the Blue row listing, I want to keep the BLUE type rows or if colors other than BLUE are dated within 90 days previous, remove them.
Any clues to the best approach for this? I guess I am having trouble envisioning the subquery filters. Thank you!