Have a fact table joined with dimension tables to pull out specific information.
I want to pull out id’s where the status = running and they must have the running status between several dates.

• If between the dates specified, the status changes to other than "running" that campaign is not counted for in the result.

I have an idea of how to approach it, but i don’t think my logic and syntax is exactly correct. As I think the query below would pull data for 12345 which is not intended.

The result expected should only show information for id 98765

Current SQL

SELECT
*
From....
Where status='running' 
AND iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
Group by 1,2,3

I believe I am likely missing a "NOT" condition somewhere to further filter out the dates.
Or the other thought is that id must have running between those dates, however, I am not sure how to apply that logic and its syntax. Any help would be appreciated.

>Solution :

Instead of selecting those that had status = 'running', you could filter out those that don’t have status = 'running', in the specified time frame.

SELECT DISTINCT id 
FROM tab
WHERE iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
  AND id NOT IN (SELECT id 
                 FROM tab 
                 WHERE NOT status = 'running')

Check the demo here.