Worklist

select
    w.id as worklist_id
  , w.claim_id
  , c.local_id as claim_local_id
  , c.practice_id
  , c.patient_id
  , c.rendering_provider_name
  , w.balance
  , w.created_timestamp as worklisted_at
from worklist w
  join claim c on c.id = w.claim_id
where w.is_deleted = false
  and c.is_deleted = false
order by w.balance desc;

select
    c.practice_id
  , count(*) as worklisted_claims
  , sum(w.balance) as total_balance
  , avg(w.balance) as avg_balance
from worklist w
  join claim c on c.id = w.claim_id
where w.is_deleted = false
  and c.is_deleted = false
group by c.practice_id
order by total_balance desc;

select
    w.claim_id
  , c.local_id as claim_local_id
  , c.practice_id
  , c.rendering_provider_name
  , w.balance
  , w.created_timestamp as worklisted_at
  , datediff(day, w.created_timestamp, current_date) as days_on_worklist
from worklist w
  join claim c on c.id = w.claim_id
where w.is_deleted = false
  and c.is_deleted = false
  and w.created_timestamp < dateadd(day, -30, current_date)
order by days_on_worklist desc;