I'm not even sure what to call this type of query and that's why the title might be misleading. Here's what I want to do. We have a history table that goes like this

id, mod_date, is_active
1, 2022-06-22:12:00:00, 1
1, 2022-06-22:13:00:00, 0
2, 2022-06-22:12:00:00, 0
3, 2022-07-07:00:00:00, 1

is_active means that the record was made active. For example, row 1 was made active at 2022-06-22:12:00:00 and then was made inactive at 13:00:00.

What I want is to get only the row that was made inactive on a specific day and not made active again on that day. I came up with this query

select distinct(id)
from history
where is_active = 0
and cast(ah.mod_date as date) = '2022-06-22'

It would return 1 and 2. But I only want 2 because 1 was toggled between states. So, I only want to find all of ids that was made inactive on a specific day and never made active again on that day or any of the toggling the same day.

CodePudding user response：

You may phrase this using exists logic:

SELECT *
FROM history h1
WHERE is_active = 0 AND mod_date::date = '2022-06-22' AND
      NOT EXISTS (SELECT 1
                  FROM history h2
                  WHERE h2.mod_date::date = '2022-06-22' AND
                        h2.id = h1.id AND h2.is_active = 1);

CodePudding user response：

Count how many times an id has been activated and deactivated in a day. From the result select the ones that have been deactivated once and activated zero times.

with the_historical_table(id, mod_date, is_active) as 
(
 values
  (1, '2022-06-22:12:00:00', 1),
  (1, '2022-06-22:13:00:00', 0),
  (2, '2022-06-22:12:00:00', 0),
  (3, '2022-07-07:00:00:00', 1)
)
select id, mod_date from
(
 select id, mod_date::date, 
    count(*) filter (where is_active = 1) activated,
    count(*) filter (where is_active = 0) deactivated
 from the_historical_table
 group by id, mod_date::date
) t
where activated = 0 and deactivated = 1;

Result: