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

UPDATE note SET visit_number = ((SELECT COUNT(*)
 FROM note n WHERE n.id < note.id AND n.client_id = note.client_id AND n.is_cancelled IS NOT TRUE AND n.is_core_note IS FALSE) + 1) WHERE is_cancelled IS NOT TRUE;

COPY (
  SELECT 
    name_first,
    name_last,
    next_mcp_appointment_date, 
    (SELECT data FROM point WHERE category = 'CHIEF_COMPLAINT' AND point.client_id = client.id ORDER BY point.created_at DESC LIMIT 1) AS latest_chief_complaint,
    client_engagement_status_category as status
  FROM client 
  WHERE shadow_pro_id IS NULL
) TO '/tmp/client_appts.csv' CSV HEADER;


COPY (
  SELECT 
    cl.name_first,
    cl.name_last,
    appt.raw_date, 
    appt.status,
    mcp.name_first, 
    mcp.name_last
  FROM appointment  appt 
  LEFT JOIN pro mcp ON appt.pro_id = mcp.id 
  LEFT JOIN client cl ON appt.client_id = cl.id 
  WHERE appt.raw_date >= '01-05-2022'::DATE
  AND cl.shadow_pro_id IS NOT NULL
) TO '/tmp/appts.csv' CSV HEADER;


UPDATE care_month SET days_between_most_recent_mcp_note_date_and_end_of_care_month = 
(((date_trunc('month', start_date) + interval '1 month' - interval '1 day')::date) - most_recent_mcp_note_date ) 
WHERE days_between_most_recent_mcp_note_date_and_end_of_care_month IS NULL AND most_recent_mcp_note_date IS NOT NULL;


UPDATE care_month SET most_recent_mcp_note_date = (SELECT effective_dateest FROM note WHERE note.client_id = care_month.client_id AND is_signed_by_hcp IS TRUE AND is_cancelled IS NOT TRUE AND effective_dateest <  ((date_trunc('month', start_date) + interval '1 month' - interval '1 day')::date) ORDER BY effective_dateest DESC LIMIT 1)
WHERE most_recent_mcp_note_date IS NULL;


UPDATE bill SET effective_date = (SELECT (date_trunc('month', care_month.start_date) + interval '1 month' - interval '1 day')::DATE FROM care_month WHERE bill.care_month_id = care_month.id)
WHERE bill.care_month_id IS NOT NULL and bill.effective_date IS NULL;

UPDATE bill SET effective_date = (SELECT effective_dateest FROM note WHERE bill.note_id = note.id) WHERE bill.note_id IS NOT NULL AND bill.effective_date IS NULL;

SELECT most_recent_mcp_note_date FROM care_month WHERE uid = '6d437394-902e-4899-a2bd-bd1702825461';