-1

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!

enter image description here

0
Select * from your_table a
  WHERE date_of_service BETWEEN CAST ('2020-01-01' AS DATE) AND  CAST ('2020-09-30' AS DATE)
  AND type = 'BLUE'
  And not exists (select null from your_table b
                    Where b.id = a.id 
                      And b.type != a.type
                      And datediff(day,b.date_of_service,a.date_of_service) < 90)
| improve this answer | |
  • Thank you! This worked for me. The combo of a NOT EXISTS and SELECT NULL subquery is a new one for me. – minnemike Oct 15 at 14:19
  • I wouldn't want to waste cpu cycles on moving data in memory just to throw it away. ;-) – Gerard H. Pille Oct 15 at 17:13
  • An elegant and succinct solution. Thanks again! Cheers... :) – minnemike Oct 16 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.