stagfe2/spec/rpt-queries.sql

-- no longer active AND device-last-used within :xyz
-- how long since last visit

-- patients created on/after 2022-01-09 without MCP
select count(id)
from client where shadow_pro_id is null and mcp_pro_id is null and created_at::date >= '2022-01-09'


select client.id, client.uid, name_first, name_last, cell_number, source, initiative, client_engagement_status_category,
       cpc.commercial_payer_name,
       (cpc.auto_medicare_detail_json::json)->'plan_details'->'MD'->'payer_name'
from client left join client_primary_coverage cpc on client.latest_client_primary_coverage_id = cpc.id
where shadow_pro_id is null and mcp_pro_id is null and client.created_at::date >= '2022-01-09'
  --  and cpc.auto_medicare_is_match_found = true
order by name_first;




select client.created_at, client.id, client.uid, name_first, name_last, cell_number, source, initiative, client_engagement_status_category,
       (select count(id) from note where note.is_cancelled is false and note.is_signed_by_hcp is true and note.client_id = client.id) as num_notes
from client
where
        (select count(id) from note where note.is_cancelled is false and note.is_signed_by_hcp is true and note.client_id = client.id) > 0 AND
    shadow_pro_id is null and client.created_at::date >= '2022-01-09' and (client.client_engagement_status_category <> 'DUMMY' OR client.client_engagement_status_category is null)
  and client.is_part_b_primary = 'YES'
order by created_at desc;



select count(client.id),
       (select pro.name_display from pro where pro.id = client.mcp_pro_id) as mcp,
       client.mcp_pro_id, client.created_at::date
from client
where
        (select count(id) from note where note.is_cancelled is false and note.is_signed_by_hcp is true and note.client_id = client.id) > 0 AND
    shadow_pro_id is null and client.created_at::date >= '2022-01-16' and (client.client_engagement_status_category <> 'DUMMY' OR client.client_engagement_status_category is null)
  -- and client.is_part_b_primary = 'YES'
group by mcp_pro_id, client.created_at::date
order by client.created_at::date desc, count desc


select count(client.id),
       (select pro.name_display from pro where pro.id = client.created_by_pro_id) as creator,
       client.created_by_pro_id, client.created_at::date
from client
where
        (select count(id) from note where note.is_cancelled is false and note.is_signed_by_hcp is true and note.client_id = client.id) > 0 AND
    shadow_pro_id is null and client.created_at::date >= '2022-01-16' and (client.client_engagement_status_category <> 'DUMMY' OR client.client_engagement_status_category is null)
  -- and client.is_part_b_primary = 'YES'
group by created_by_pro_id, client.created_at::date
order by client.created_at::date desc, count desc



select client.created_at, client.id, client.uid, name_first, name_last, cell_number, source, initiative, client_engagement_status_category,
       (select count(id) from note where note.is_cancelled is false and note.is_signed_by_hcp is true and note.client_id = client.id) as num_notes,
       client.mailing_address_state
from client
where
        (select count(id) from note where note.is_cancelled is false and note.is_signed_by_hcp is true and note.client_id = client.id) > 0 AND
    shadow_pro_id is null and client.created_at::date >= '2022-01-09' and (client.client_engagement_status_category <> 'DUMMY' OR client.client_engagement_status_category is null)
  and client.is_part_b_primary = 'YES' and mcp_pro_id = 1175
order by created_at desc;



SELECT
    client.name_first, client.name_last, (select pro.name_display from pro where pro.id = client.mcp_pro_id) as mcp, client.most_recent_completed_mcp_note_date, client.client_engagement_status_category,
    (SELECT COUNT(*) FROM measurement m WHERE m.client_id = client.id AND m.is_cellular_zero is false and m.ts_date_time::date >= '2021-11-01' AND ts_date_time::date <= '2021-11-30' and m.is_removed is false) as measurements
FROM client
WHERE
        id IN (SELECT client_id FROM client_bdt_device) -- have devices
  AND is_part_b_primary = 'YES'  -- are part b primary
  -- have 16+ measurements in dec
  AND (SELECT COUNT(*) FROM measurement m WHERE m.client_id = client.id AND m.is_cellular_zero is false and m.ts_date_time::date >= '2021-11-01' AND ts_date_time::date <= '2021-11-30' and m.is_removed is false) > 0
  -- have not been see in over 60 days
  AND client.most_recent_completed_mcp_note_date < (now() - interval '200 day')::date


SELECT
    count(client.id), (select pro.name_display from pro where pro.id = client.mcp_pro_id) as mcp
FROM client
WHERE
        id IN (SELECT client_id FROM client_bdt_device) -- have devices
  AND is_part_b_primary = 'YES'  -- are part b primary
  -- have 16+ measurements in dec
  AND (SELECT COUNT(distinct(m.created_at::date)) FROM measurement m WHERE m.client_id = client.id AND m.is_cellular_zero is false and m.created_at::date >= '2021-11-01' AND m.created_at::date <= '2021-11-30' and m.is_removed is false) > 0
  -- have not been see in over 60 days
  AND client.most_recent_completed_mcp_note_date < (now() - interval '100 day')::date
group by mcp_pro_id


-- DATE | MCP | COUNT