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.

CodePudding user response：

you could do a case statement to compare the running count to the total count.

select id,
count('dracula') cnt,
sum(case when status = 'running' then 1 else 0 end) cnt2
from mytable
where iddate BETWEEN DATE('2022-06-09') AND DATE('2022-06-11')
group by id
having cnt = cnt2

here is the fiddle. https://www.db-fiddle.com/f/fmFcwnGXyovYezBSCQ4aac/0

CodePudding user response：

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

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

Check the demo here.