Claim Note

select
    n.id as claim_note_id
  , n.created_timestamp
  , concat(u.first_name, ' ', u.last_name) as author_name
  , u.email as author_email
  , n.is_statement_note
  , n.note
from claim_note n
  left join elation_billing_user u on u.id = n.elation_billing_user_id
where n.is_deleted = false
  and n.claim_id = 1234567890  -- Replace with actual claim ID
order by n.created_timestamp;

select
    c.id as claim_id
  , c.local_id
  , c.from_date
  , c.is_billed
  , n.created_timestamp as last_note_timestamp
  , concat(u.first_name, ' ', u.last_name) as last_author_name
  , n.note as last_note
from claim c
  inner join claim_note n on n.claim_id = c.id
  left join elation_billing_user u on u.id = n.elation_billing_user_id
where c.is_deleted = false
  and n.is_deleted = false
qualify row_number() over (partition by c.id order by n.created_timestamp desc) = 1
order by n.created_timestamp desc
limit 100;

select
    concat(u.first_name, ' ', u.last_name) as author_name
  , u.email as author_email
  , count(*) as notes_written
  , count(distinct n.claim_id) as distinct_claims
  , max(n.created_timestamp) as most_recent_note
from claim_note n
  left join elation_billing_user u on u.id = n.elation_billing_user_id
where n.is_deleted = false
  and n.created_timestamp >= dateadd(day, -90, current_date)
group by u.first_name, u.last_name, u.email
order by notes_written desc;

select
    pat.first_name
  , pat.last_name
  , c.id as claim_id
  , c.local_id
  , n.created_timestamp
  , concat(u.first_name, ' ', u.last_name) as author_name
  , n.note
from claim_note n
  inner join claim c on c.id = n.claim_id
  left join patient pat on pat.id = c.patient_id
  left join elation_billing_user u on u.id = n.elation_billing_user_id
where n.is_deleted = false
  and n.is_statement_note = true
  and c.patient_id = 1234567890  -- Replace with actual patient ID
order by n.created_timestamp desc;