In home care, the schedule, the care plan, and the shift notes each describe what's supposed to be happening in a client's home. They rarely all agree on what actually is happening, and reconciling the gaps is where a lot of the operational work goes.

-- one row per visit showing hours drift between EVV,
-- schedule, and the care plan authorization. CTE avoids
-- the alias-in-same-SELECT problem in Postgres.
WITH visit_hours AS (
  SELECT
    v.visit_id,
    v.client_id,
    v.caregiver_id,
    ROUND(EXTRACT(EPOCH FROM
      (e.clock_out - e.clock_in)) / 3600.0, 2
    )                             AS hours_actual,
    ROUND(EXTRACT(EPOCH FROM
      (s.scheduled_end - s.scheduled_start)) / 3600.0, 2
    )                             AS hours_scheduled,
    a.authorized_hours_per_day    AS hours_authorized,
    c.carrier_id
  FROM visits v
  JOIN evv_clockins e             USING (visit_id)
  JOIN scheduled_shifts s         USING (visit_id)
  JOIN care_plan_authorizations a USING (client_id)
  WHERE v.visit_date
    BETWEEN current_date - interval '30 days'
        AND current_date
)
SELECT
  vh.visit_id,
  cl.client_name,
  cg.caregiver_name,
  vh.hours_actual,
  vh.hours_scheduled,
  vh.hours_authorized,
  vh.hours_actual - LEAST(
    vh.hours_scheduled, vh.hours_authorized
  )                               AS drift_hours,
  (vh.hours_actual - LEAST(
    vh.hours_scheduled, vh.hours_authorized
  )) * r.rate                     AS dollar_drift
FROM visit_hours vh
JOIN clients cl     USING (client_id)
JOIN caregivers cg  USING (caregiver_id)
JOIN carrier_reimbursement_rates r
  ON r.carrier_id = vh.carrier_id
WHERE ABS(vh.hours_actual
          - vh.hours_scheduled) > 0.1
ORDER BY dollar_drift DESC;