data.ml
SQL Genomics health services

A R

A R model for genomics health services.

source-preserved

Blocks

a_r.sql · 75 lines
inferred 
-- models/a_r.sql
{{ config(materialized='view') }}

SELECT
  "order".id AS id
  ,claim.id as claim_id
  , coalesce(total_charges,0) AS claim_total_charges
  , CASE WHEN eob.eob_total_paid is NULL THEN coalesce(ap.Insurance_Inv_Pmts,0) ELSE eob.eob_total_paid END as paid
  , sum(coalesce(denial_adjustments,0)) as denial_adjustments
  , coalesce(eob.eob_total_real_pr,0) as real_pr
  , coalesce(Customer_Inv_Pmts,0) as customer_inv_pmts
  , coalesce(Comp_Amt,0) as comp_amt
  , "order".completed_on AS completed_on
  , "order".create_date AS create_date
  , final_allowed.actual_asp AS allowed
  , terminal_state.terminal_bool AS terminal_bool
  , "order".bill_type AS bill_type
  , final_allowed.adjusted_allowed AS adjusted_allowed
  , claim.status_name AS claim_status_name
  , claim_states.status_state as status_state
  , invoice.amount
FROM
  ${SCHEMA}.order AS "order"
LEFT JOIN
  ${SCHEMA}.insuranceclaim as claim on claim.order_id = "order".id
LEFT JOIN
  ${SCHEMA}.invoice as invoice on invoice.id = claim.patient_invoice_id
LEFT JOIN
  {{ ref('aggregated_payments') }} as ap on ap.id = "order".id
LEFT JOIN
  {{ ref('eob_summary') }} as eob on eob.order_id = "order".id
LEFT JOIN
  {{ ref('eobadjustmentsummary') }} as e on e.order_id = "order".id
LEFT JOIN
  {{ ref('final_allowed') }} as final_allowed on final_allowed.order_id = "order".id
LEFT JOIN
  {{ ref('terminal_state') }} AS terminal_state ON terminal_state.id="order".id
LEFT JOIN
  ${SCHEMA}.claim_states on claim_states.status_name = claim.status_name
WHERE
  "order".bill_type = 'in'
  and claim.status_name != 'Canceled'
  and claim.status_name != 'Canceled Chose Consignment'
  and claim.status_name != 'Canceled Chose Cash'
GROUP BY
  1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17

# models/schema.yml
version: 2

models:
  - name: a_r
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: claim_status_name
      - name: status_state
      - name: claim_id
      - name: completed_quarter
      - name: is_this_quarter
      - name: is_last_quarter
      - name: terminal_bool
      - name: claim_total_charges
      - name: allowed
      - name: adjusted_allowed
      - name: invoice_amount
      - name: moop_adjusted_charges
      - name: paid
      - name: denial_adjustments
      - name: real_pr
      - name: customer_inv_pmts
      - name: comp_amt
      - name: AR_age

Assumptions

  • Each ${view.SQL_TABLE_NAME} was mapped to {{ ref('view') }}; confirm a dbt model exists for each referenced view.
  • materialized='view' is the default; no persist hint found.
  • schema.yml columns come from view dimensions; a primary_key dimension gets unique + not_null tests.
a_r.sql · 43 lines
source-preserved validation: not checked 
SELECT
  "order".id AS id
  ,claim.id as claim_id
  , coalesce(total_charges,0) AS claim_total_charges
  , CASE WHEN eob.eob_total_paid is NULL THEN coalesce(ap.Insurance_Inv_Pmts,0) ELSE eob.eob_total_paid END as paid
  , sum(coalesce(denial_adjustments,0)) as denial_adjustments
  , coalesce(eob.eob_total_real_pr,0) as real_pr
  , coalesce(Customer_Inv_Pmts,0) as customer_inv_pmts
  , coalesce(Comp_Amt,0) as comp_amt
  , "order".completed_on AS completed_on
  , "order".create_date AS create_date
  , final_allowed.actual_asp AS allowed
  , terminal_state.terminal_bool AS terminal_bool
  , "order".bill_type AS bill_type
  , final_allowed.adjusted_allowed AS adjusted_allowed
  , claim.status_name AS claim_status_name
  , claim_states.status_state as status_state
  , invoice.amount
FROM
  ${SCHEMA}.order AS "order"
LEFT JOIN
  ${SCHEMA}.insuranceclaim as claim on claim.order_id = "order".id
LEFT JOIN
  ${SCHEMA}.invoice as invoice on invoice.id = claim.patient_invoice_id
LEFT JOIN
  ${aggregated_payments.SQL_TABLE_NAME} as ap on ap.id = "order".id
LEFT JOIN
  ${eob_summary.SQL_TABLE_NAME} as eob on eob.order_id = "order".id
LEFT JOIN
  ${eobadjustmentsummary.SQL_TABLE_NAME} as e on e.order_id = "order".id
LEFT JOIN
  ${final_allowed.SQL_TABLE_NAME} as final_allowed on final_allowed.order_id = "order".id
LEFT JOIN
  ${terminal_state.SQL_TABLE_NAME} AS terminal_state ON terminal_state.id="order".id
LEFT JOIN
  ${SCHEMA}.claim_states on claim_states.status_name = claim.status_name
WHERE
  "order".bill_type = 'in'
  and claim.status_name != 'Canceled'
  and claim.status_name != 'Canceled Chose Consignment'
  and claim.status_name != 'Canceled Chose Cash'
GROUP BY
  1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17
a_r.view.lkml · 394 lines
inferred 
view: a_r {
  derived_table: {
    sql: SELECT
  "order".id AS id
  ,claim.id as claim_id
  , coalesce(total_charges,0) AS claim_total_charges
  , CASE WHEN eob.eob_total_paid is NULL THEN coalesce(ap.Insurance_Inv_Pmts,0) ELSE eob.eob_total_paid END as paid
  , sum(coalesce(denial_adjustments,0)) as denial_adjustments
  , coalesce(eob.eob_total_real_pr,0) as real_pr
  , coalesce(Customer_Inv_Pmts,0) as customer_inv_pmts
  , coalesce(Comp_Amt,0) as comp_amt
  , "order".completed_on AS completed_on
  , "order".create_date AS create_date
  , final_allowed.actual_asp AS allowed
  , terminal_state.terminal_bool AS terminal_bool
  , "order".bill_type AS bill_type
  , final_allowed.adjusted_allowed AS adjusted_allowed
  , claim.status_name AS claim_status_name
  , claim_states.status_state as status_state
  , invoice.amount
FROM
  ${SCHEMA}.order AS "order"
LEFT JOIN
  ${SCHEMA}.insuranceclaim as claim on claim.order_id = "order".id
LEFT JOIN
  ${SCHEMA}.invoice as invoice on invoice.id = claim.patient_invoice_id
LEFT JOIN
  ${aggregated_payments.SQL_TABLE_NAME} as ap on ap.id = "order".id
LEFT JOIN
  ${eob_summary.SQL_TABLE_NAME} as eob on eob.order_id = "order".id
LEFT JOIN
  ${eobadjustmentsummary.SQL_TABLE_NAME} as e on e.order_id = "order".id
LEFT JOIN
  ${final_allowed.SQL_TABLE_NAME} as final_allowed on final_allowed.order_id = "order".id
LEFT JOIN
  ${terminal_state.SQL_TABLE_NAME} AS terminal_state ON terminal_state.id="order".id
LEFT JOIN
  ${SCHEMA}.claim_states on claim_states.status_name = claim.status_name
WHERE
  "order".bill_type = 'in'
  and claim.status_name != 'Canceled'
  and claim.status_name != 'Canceled Chose Consignment'
  and claim.status_name != 'Canceled Chose Cash'
GROUP BY
  1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17 ;;
  }

  dimension: id {
    type: int
    primary_key: yes
    hidden: yes
    sql: ${TABLE}.id ;;
  }

  dimension: claim_status_name {
    hidden: yes
    sql: ${TABLE}.claim_status_name ;;
  }

  dimension: status_state {
    hidden: yes
    sql: ${TABLE}.status_state ;;
  }

  dimension: claim_id {
    type: int
    hidden: yes
    sql: ${TABLE}.claim_id ;;
  }

  dimension: completed_quarter {
    hidden: yes
    sql: cast(EXTRACT(year FROM ${TABLE}.completed_on) as varchar)
||
' - Q'
||
cast(EXTRACT(quarter FROM ${TABLE}.completed_on) as varchar) ;;
  }

  dimension: is_this_quarter {
    type: yesno
    hidden: yes
    sql: ${completed_quarter} = cast(EXTRACT(year FROM NOW()) as varchar)
||
' - Q'
||
cast(EXTRACT(quarter FROM NOW()) as varchar) ;;
  }

  dimension: is_last_quarter {
    type: yesno
    hidden: yes
    sql: ${completed_quarter} = cast(EXTRACT(year FROM (NOW() - INTERVAL '90 days')) as varchar)
||
' - Q'
||
cast(EXTRACT(quarter FROM (NOW()- INTERVAL '90 days')) as varchar) ;;
  }

  dimension: terminal_bool {
    type: yesno
    hidden: yes
    sql: ${TABLE}.terminal_bool ;;
  }

  dimension: claim_total_charges {
    type: number
    hidden: yes
    sql: ${TABLE}.claim_total_charges ;;
  }

  dimension: allowed {
    hidden: yes
    sql: ${TABLE}.allowed ;;
  }

  dimension: adjusted_allowed {
    hidden: yes
    sql: ${TABLE}.adjusted_allowed ;;
  }

  dimension: invoice_amount {
    hidden: yes
    sql: ${TABLE}.amount ;;
  }

  dimension: moop_adjusted_charges {
    type: number
    hidden: yes
    sql: CASE WHEN ${claim_status_name} = 'Maximum OOP - No Insurance' THEN ${invoice_amount} ELSE ${claim_total_charges} END ;;
  }

  dimension: paid {
    type: number
    hidden: yes
    sql: ${TABLE}.paid ;;
  }

  dimension: denial_adjustments {
    type: number
    hidden: yes
    sql: ${TABLE}.denial_adjustments ;;
  }

  dimension: real_pr {
    type: number
    hidden: yes
    sql: ${TABLE}.real_pr ;;
  }

  dimension: customer_inv_pmts {
    type: number
    hidden: yes
    sql: ${TABLE}.customer_inv_pmts ;;
  }

  dimension: comp_amt {
    type: number
    hidden: yes
    sql: ${TABLE}.comp_amt ;;
  }

  dimension: AR_age {
    label: "Age"
    type: string
    sql: CASE WHEN ${order.completed_date} < (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-120 || ' day')::INTERVAL)) THEN 'Over 120 Days'
  WHEN  ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-30 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-30 || ' day')::INTERVAL) + (30 || ' day')::INTERVAL)) THEN '0-30 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-45 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-45 || ' day')::INTERVAL) + (15 || ' day')::INTERVAL)) THEN '30-45 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-60 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-60 || ' day')::INTERVAL) + (15 || ' day')::INTERVAL)) THEN '45-60 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-90 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-90 || ' day')::INTERVAL) + (30 || ' day')::INTERVAL)) THEN '60-90 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-120 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-120 || ' day')::INTERVAL) + (30 || ' day')::INTERVAL)) THEN '90-120 Days'
  WHEN ${order.completed_date} IS NULL AND ${claim_status_name} != 'Maximum OOP - No Insurance' THEN 'Unbilled'
  ELSE NULL END ;;
  }

  dimension_group: completed {
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.completed_on ;;
  }

  dimension_group: created {
    type: time
    timeframes: [date, week, month, year]
    sql: ${TABLE}.create_date ;;
  }

  measure: sum_allowed {
    type: sum
    hidden: yes
    sql: ${allowed} ;;
  }

  measure: sum_adjusted_allowed {
    type: sum
    hidden: yes
    value_format: "$#,###"
    sql: ${adjusted_allowed} ;;
  }

  measure: sum_allowed_quarter {
    type: sum
    hidden: yes
    sql: ${allowed} ;;
  }

  measure: sum_projected_allowed_lookback_90_days {
    type: sum
    hidden: yes
    sql: ${adjusted_allowed} ;;
  }

  measure: daily_average_allowed_for_lookback_period {
    type: number
    hidden: yes
    sql: ${sum_projected_allowed_lookback_90_days}/90.00 ;;
  }

  measure: sum_projected_allowed_lookback_90_days_ago_for_90 {
    type: sum
    hidden: yes
    sql: ${adjusted_allowed} ;;
  }

  measure: daily_average_allowed_for_lookback_period_no_skip {
    type: number
    hidden: yes
    sql: ${sum_projected_allowed_lookback_90_days_ago_for_90}/90.00 ;;
  }

  measure: patient_ar {
    label: "Patient Only AR"
    type: sum
    value_format: "$#,###"
    sql: ${invoice_amount} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: payer_only_ar {
    label: "Payer Only AR"
    type: sum
    value_format: "$#,###"
    sql: CASE WHEN ${status_state} = 'Terminal State' THEN 0 ELSE ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} END ;;
  }

  measure: AR {
    label: "AR"
    type: sum
    value_format: "$#,##0"
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: ar_count {
    label: "AR Count"
    type: sum
    drill_fields: [detail*]
    sql: CASE WHEN (${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}) = 0 THEN 0 ELSE 1 END ;;
  }

  measure: ar_count_payer_only {
    label: "Payer Only AR Count"
    type: sum
    drill_fields: [detail*]
    sql: CASE WHEN  ${status_state} = 'Terminal State' AND (${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}) = 0 THEN 0 ELSE 1 END ;;
  }

  measure: percent_of_total_AR {
    label: "Percent of Total Payer-Only A/R"
    type: percent_of_total
    sql: ${payer_only_ar} ;;
  }

  measure: AR_quarter {
    type: sum
    value_format: "$#,##0"
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: AR_aged_30 {
    type: sum
    hidden: yes
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: AR_aged_45 {
    type: sum
    hidden: yes
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: AR_aged_60 {
    type: sum
    hidden: yes
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: AR_aged_90 {
    type: sum
    hidden: yes
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: percent_AR_over_30_days {
    type: number
    hidden: yes
    value_format: "#.00%"
    sql: coalesce(1.00*${AR_aged_30}/nullif(${AR},0),0) ;;
  }

  measure: percent_AR_over_45_days {
    type: number
    hidden: yes
    value_format: "#.00%"
    sql: coalesce(1.00*${AR_aged_45}/nullif(${AR},0),0) ;;
  }

  measure: percent_AR_over_60_days {
    type: number
    hidden: yes
    value_format: "#.00%"
    sql: coalesce(1.00*${AR_aged_60}/nullif(${AR},0),0) ;;
  }

  measure: percent_AR_over_90_days {
    type: number
    hidden: yes
    value_format: "#.00%"
    sql: coalesce(1.00*${AR_aged_90}/nullif(${AR},0),0) ;;
  }

  measure: AR_aged_120_payer_only {
    type: sum
    hidden: yes
    value_format: "$#,###"
    sql: CASE WHEN ${status_state} = 'Terminal State' THEN 0 ELSE ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} END ;;
  }

  measure: percent_AR_over_120_days {
    type: number
    value_format: "#.00%"
    sql: coalesce(1.00*${AR_aged_120_payer_only}/nullif(${payer_only_ar},0),0) ;;
  }

  measure: AR_post {
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: AR_pre {
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} ;;
  }

  measure: percent_AR_post {
    type: number
    value_format: "#.00%"
    sql: coalesce(1.00*${AR_post}/nullif(${AR},0),0) ;;
  }

  measure: percent_AR_pre {
    type: number
    value_format: "#.00%"
    sql: coalesce(1.00*${AR_pre}/nullif(${AR},0),0) ;;
  }

  measure: sum_total_charges_quarter {
    type: sum
    hidden: yes
    sql: ${claim_total_charges} ;;
  }

  measure: sum_total_charges_last_month {
    type: sum
    hidden: yes
    sql: ${claim_total_charges} ;;
  }

  measure: sum_total_charges {
    type: sum
    hidden: yes
    sql: ${claim_total_charges} ;;
  }

  measure: DSO {
    label: "DSO"
    type: number
    value_format: "#\" days\""
    sql: coalesce(${payer_only_ar}/nullif(${daily_average_allowed_for_lookback_period_no_skip},0),0) ;;
  }
}
a_r.view.lookml · 278 lines
source-preserved 
- view: a_r
  derived_table:
    sql: SELECT
  "order".id AS id
  ,claim.id as claim_id
  , coalesce(total_charges,0) AS claim_total_charges
  , CASE WHEN eob.eob_total_paid is NULL THEN coalesce(ap.Insurance_Inv_Pmts,0) ELSE eob.eob_total_paid END as paid
  , sum(coalesce(denial_adjustments,0)) as denial_adjustments
  , coalesce(eob.eob_total_real_pr,0) as real_pr
  , coalesce(Customer_Inv_Pmts,0) as customer_inv_pmts
  , coalesce(Comp_Amt,0) as comp_amt
  , "order".completed_on AS completed_on
  , "order".create_date AS create_date
  , final_allowed.actual_asp AS allowed
  , terminal_state.terminal_bool AS terminal_bool
  , "order".bill_type AS bill_type
  , final_allowed.adjusted_allowed AS adjusted_allowed
  , claim.status_name AS claim_status_name
  , claim_states.status_state as status_state
  , invoice.amount
FROM
  ${SCHEMA}.order AS "order"
LEFT JOIN
  ${SCHEMA}.insuranceclaim as claim on claim.order_id = "order".id
LEFT JOIN
  ${SCHEMA}.invoice as invoice on invoice.id = claim.patient_invoice_id
LEFT JOIN
  ${aggregated_payments.SQL_TABLE_NAME} as ap on ap.id = "order".id
LEFT JOIN
  ${eob_summary.SQL_TABLE_NAME} as eob on eob.order_id = "order".id
LEFT JOIN
  ${eobadjustmentsummary.SQL_TABLE_NAME} as e on e.order_id = "order".id
LEFT JOIN
  ${final_allowed.SQL_TABLE_NAME} as final_allowed on final_allowed.order_id = "order".id
LEFT JOIN
  ${terminal_state.SQL_TABLE_NAME} AS terminal_state ON terminal_state.id="order".id
LEFT JOIN
  ${SCHEMA}.claim_states on claim_states.status_name = claim.status_name
WHERE
  "order".bill_type = 'in'
  and claim.status_name != 'Canceled'
  and claim.status_name != 'Canceled Chose Consignment'
  and claim.status_name != 'Canceled Chose Cash'
GROUP BY
  1,2,3,4,6,7,8,9,10,11,12,13,14,15,16,17
  fields:
  - dimension: id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.id
  - dimension: claim_status_name
    hidden: true
    sql: ${TABLE}.claim_status_name
  - dimension: status_state
    hidden: true
    sql: ${TABLE}.status_state
  - dimension: claim_id
    type: int
    hidden: true
    sql: ${TABLE}.claim_id
  - dimension: completed_quarter
    hidden: true
    sql: cast(EXTRACT(year FROM ${TABLE}.completed_on) as varchar)
||
' - Q'
||
cast(EXTRACT(quarter FROM ${TABLE}.completed_on) as varchar)
  - dimension: is_this_quarter
    type: yesno
    hidden: true
    sql: ${completed_quarter} = cast(EXTRACT(year FROM NOW()) as varchar)
||
' - Q'
||
cast(EXTRACT(quarter FROM NOW()) as varchar)
  - dimension: is_last_quarter
    type: yesno
    hidden: true
    sql: ${completed_quarter} = cast(EXTRACT(year FROM (NOW() - INTERVAL '90 days')) as varchar)
||
' - Q'
||
cast(EXTRACT(quarter FROM (NOW()- INTERVAL '90 days')) as varchar)
  - dimension: terminal_bool
    type: yesno
    hidden: true
    sql: ${TABLE}.terminal_bool
  - dimension: claim_total_charges
    type: number
    hidden: true
    sql: ${TABLE}.claim_total_charges
  - dimension: allowed
    hidden: true
    sql: ${TABLE}.allowed
  - dimension: adjusted_allowed
    hidden: true
    sql: ${TABLE}.adjusted_allowed
  - dimension: invoice_amount
    hidden: true
    sql: ${TABLE}.amount
  - dimension: moop_adjusted_charges
    type: number
    hidden: true
    sql: CASE WHEN ${claim_status_name} = 'Maximum OOP - No Insurance' THEN ${invoice_amount} ELSE ${claim_total_charges} END
  - dimension: paid
    type: number
    hidden: true
    sql: ${TABLE}.paid
  - dimension: denial_adjustments
    type: number
    hidden: true
    sql: ${TABLE}.denial_adjustments
  - dimension: real_pr
    type: number
    hidden: true
    sql: ${TABLE}.real_pr
  - dimension: customer_inv_pmts
    type: number
    hidden: true
    sql: ${TABLE}.customer_inv_pmts
  - dimension: comp_amt
    type: number
    hidden: true
    sql: ${TABLE}.comp_amt
  - dimension: AR_age
    type: string
    label: Age
    sql: CASE WHEN ${order.completed_date} < (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-120 || ' day')::INTERVAL)) THEN 'Over 120 Days'
  WHEN  ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-30 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-30 || ' day')::INTERVAL) + (30 || ' day')::INTERVAL)) THEN '0-30 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-45 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-45 || ' day')::INTERVAL) + (15 || ' day')::INTERVAL)) THEN '30-45 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-60 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-60 || ' day')::INTERVAL) + (15 || ' day')::INTERVAL)) THEN '45-60 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-90 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-90 || ' day')::INTERVAL) + (30 || ' day')::INTERVAL)) THEN '60-90 Days'
  WHEN ${order.completed_date} >= (SELECT ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-120 || ' day')::INTERVAL))
    AND ${order.completed_date} < (SELECT ( ( DATE_TRUNC('day', LOCALTIMESTAMP) + (-120 || ' day')::INTERVAL) + (30 || ' day')::INTERVAL)) THEN '90-120 Days'
  WHEN ${order.completed_date} IS NULL AND ${claim_status_name} != 'Maximum OOP - No Insurance' THEN 'Unbilled'
  ELSE NULL END
  - dimension_group: completed
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.completed_on
  - dimension_group: created
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.create_date
  - measure: sum_allowed
    type: sum
    sql: ${allowed}
  - measure: sum_adjusted_allowed
    type: sum
    value_format: '$#,###'
    sql: ${adjusted_allowed}
  - measure: sum_allowed_quarter
    type: sum
    sql: ${allowed}
  - measure: sum_projected_allowed_lookback_90_days
    type: sum
    sql: ${adjusted_allowed}
  - measure: daily_average_allowed_for_lookback_period
    type: number
    sql: ${sum_projected_allowed_lookback_90_days}/90.00
  - measure: sum_projected_allowed_lookback_90_days_ago_for_90
    type: sum
    sql: ${adjusted_allowed}
  - measure: daily_average_allowed_for_lookback_period_no_skip
    type: number
    sql: ${sum_projected_allowed_lookback_90_days_ago_for_90}/90.00
  - measure: patient_ar
    type: sum
    label: Patient Only AR
    value_format: '$#,###'
    sql: ${invoice_amount} - ${customer_inv_pmts} - ${comp_amt}
  - measure: payer_only_ar
    type: sum
    label: Payer Only AR
    value_format: '$#,###'
    sql: CASE WHEN ${status_state} = 'Terminal State' THEN 0 ELSE ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} END
  - measure: AR
    type: sum
    label: AR
    value_format: '$#,##0'
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: ar_count
    type: sum
    label: AR Count
    drill_fields:
    - detail*
    sql: CASE WHEN (${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}) = 0 THEN 0 ELSE 1 END
  - measure: ar_count_payer_only
    type: sum
    label: Payer Only AR Count
    drill_fields:
    - detail*
    sql: CASE WHEN  ${status_state} = 'Terminal State' AND (${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}) = 0 THEN 0 ELSE 1 END
  - measure: percent_of_total_AR
    type: percent_of_total
    label: Percent of Total Payer-Only A/R
    sql: ${payer_only_ar}
  - measure: AR_quarter
    type: sum
    value_format: '$#,##0'
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: AR_aged_30
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: AR_aged_45
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: AR_aged_60
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: AR_aged_90
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: percent_AR_over_30_days
    type: number
    value_format: '#.00%'
    sql: coalesce(1.00*${AR_aged_30}/nullif(${AR},0),0)
  - measure: percent_AR_over_45_days
    type: number
    value_format: '#.00%'
    sql: coalesce(1.00*${AR_aged_45}/nullif(${AR},0),0)
  - measure: percent_AR_over_60_days
    type: number
    value_format: '#.00%'
    sql: coalesce(1.00*${AR_aged_60}/nullif(${AR},0),0)
  - measure: percent_AR_over_90_days
    type: number
    value_format: '#.00%'
    sql: coalesce(1.00*${AR_aged_90}/nullif(${AR},0),0)
  - measure: AR_aged_120_payer_only
    type: sum
    value_format: '$#,###'
    sql: CASE WHEN ${status_state} = 'Terminal State' THEN 0 ELSE ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt} END
  - measure: percent_AR_over_120_days
    type: number
    value_format: '#.00%'
    sql: coalesce(1.00*${AR_aged_120_payer_only}/nullif(${payer_only_ar},0),0)
  - measure: AR_post
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: AR_pre
    type: sum
    sql: ${moop_adjusted_charges} - ${paid} - ${denial_adjustments} - ${customer_inv_pmts} - ${comp_amt}
  - measure: percent_AR_post
    type: number
    value_format: '#.00%'
    sql: coalesce(1.00*${AR_post}/nullif(${AR},0),0)
  - measure: percent_AR_pre
    type: number
    value_format: '#.00%'
    sql: coalesce(1.00*${AR_pre}/nullif(${AR},0),0)
  - measure: sum_total_charges_quarter
    type: sum
    sql: ${claim_total_charges}
  - measure: sum_total_charges_last_month
    type: sum
    sql: ${claim_total_charges}
  - measure: sum_total_charges
    type: sum
    sql: ${claim_total_charges}
  - measure: DSO
    type: number
    label: DSO
    value_format: '#" days"'
    sql: coalesce(${payer_only_ar}/nullif(${daily_average_allowed_for_lookback_period_no_skip},0),0)

Contents

  • a_r.sql
  • a_r.sql
  • a_r.view.lkml
  • a_r.view.lookml