dupe bills between systems

[fgregg]

this query helps find dupe bills between the two lis systems

with dupes as (
  select
    A.id as a_id,
    B.id as b_id
  from
    bill as A
    inner join bill as B using (title)
    inner join billaction B_action on B.id = B_action.bill_id
    left join billaction A_action on A.id = A_action.bill_id
    and A_action.date = B_action.date
    and A_action.description = B_action.description
  where
    length(A.identifier) > 13
    and length(B.identifier) < 13
    and A.identifier like '%2023%'
    and B.identifier like '%2023%'
  group by
    A.id,
    B.id
  having
    count(*) filter (
      where
        A_action.id is null
    ) = 0
)
select
  *
from
  dupes
  inner join bill A on a_id = A.id
  inner join bill B on b_id = B.id;