SQL Genomics health services

Eob Summary

Eob Summary model for genomics health services.

source-preserved

Blocks

dbt SQL LKML LookML

eob_summary.sql · 43 lines

inferred

-- models/eob_summary.sql
{{ config(materialized='view') }}

SELECT
  eob_total_charged
  , eob_total_paid
  , eob_total_allowed
  , eob_total_real_pr
  , eob_total_other_pr
  , eob_count
  , eob_total_denial_adjustments
  , claim_id
  , order_id
  , claim_total_charges
  , date_claim_received
  , date_processed_by_payer
  , first_eob_recorded_date
  , latest_eob_recorded_date
  , CASE WHEN claim_total_charges = eob_total_allowed THEN 'Fully-Paid'
    WHEN claim_total_charges = eob_total_other_pr THEN 'Full-OPR'
    WHEN claim_total_charges != eob_total_allowed AND eob_total_allowed > 0  THEN 'Partially-Paid'
    WHEN eob_total_allowed = 0 THEN 'Denied' ELSE 'Other Adj Type' END AS paid_state
FROM {{ ref('eob_summary_intermediate') }} AS es
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

# models/schema.yml
version: 2

models:
  - name: eob_summary
    columns:
      - name: claim_id
      - name: order_id
        tests:
          - unique
          - not_null
      - name: paid_state
      - name: date_claim_received
      - name: date_processed_by_payer
      - name: first_eob_recorded_date
      - name: latest_eob_recorded_date
      - name: is_latest_eob_recorded

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.

eob_summary.sql · 22 lines

source-preserved validation: not checked

SELECT
  eob_total_charged
  , eob_total_paid
  , eob_total_allowed
  , eob_total_real_pr
  , eob_total_other_pr
  , eob_count
  , eob_total_denial_adjustments
  , claim_id
  , order_id
  , claim_total_charges
  , date_claim_received
  , date_processed_by_payer
  , first_eob_recorded_date
  , latest_eob_recorded_date
  , CASE WHEN claim_total_charges = eob_total_allowed THEN 'Fully-Paid'
    WHEN claim_total_charges = eob_total_other_pr THEN 'Full-OPR'
    WHEN claim_total_charges != eob_total_allowed AND eob_total_allowed > 0  THEN 'Partially-Paid'
    WHEN eob_total_allowed = 0 THEN 'Denied' ELSE 'Other Adj Type' END AS paid_state
FROM ${eob_summary_intermediate.SQL_TABLE_NAME} AS es
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15

eob_summary.view.lkml · 148 lines

inferred

view: eob_summary {
  derived_table: {
    sql: SELECT
  eob_total_charged
  , eob_total_paid
  , eob_total_allowed
  , eob_total_real_pr
  , eob_total_other_pr
  , eob_count
  , eob_total_denial_adjustments
  , claim_id
  , order_id
  , claim_total_charges
  , date_claim_received
  , date_processed_by_payer
  , first_eob_recorded_date
  , latest_eob_recorded_date
  , CASE WHEN claim_total_charges = eob_total_allowed THEN 'Fully-Paid'
    WHEN claim_total_charges = eob_total_other_pr THEN 'Full-OPR'
    WHEN claim_total_charges != eob_total_allowed AND eob_total_allowed > 0  THEN 'Partially-Paid'
    WHEN eob_total_allowed = 0 THEN 'Denied' ELSE 'Other Adj Type' END AS paid_state
FROM ${eob_summary_intermediate.SQL_TABLE_NAME} AS es
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 ;;
  }

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

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

  dimension: paid_state {
    label: "Paid/Denied State"
    hidden: yes
    sql: ${TABLE}.paid_state ;;
  }

  dimension: date_claim_received {
    type: date
    sql: ${TABLE}.date_claim_received ;;
  }

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

  dimension: first_eob_recorded_date {
    type: time
    sql: ${TABLE}.first_eob_recorded_date ;;
  }

  dimension: latest_eob_recorded_date {
    type: time
    sql: ${TABLE}.latest_eob_recorded_date ;;
  }

  dimension: is_latest_eob_recorded {
    type: yesno
    sql: ${eob.date_recorded_time} = ${latest_eob_recorded_date_time} ;;
  }

  measure: eob_count {
    label: "EOB Count"
    type: sum
    sql: ${TABLE}.eob_count ;;
  }

  measure: eob_total_paid {
    label: "Total Payer Paid"
    type: sum
    value_format: "$#,###"
    sql: ${TABLE}.eob_total_paid ;;
  }

  measure: absolute_value_total_amount {
    label: "Payer Refund Total Amount"
    type: sum
    value_format: "$#,###"
    drill_fields: [detail2*]
    sql: CASE WHEN ${TABLE}.eob_total_paid<0 THEN ABS(${TABLE}.eob_total_paid) ELSE NULL END ;;
  }

  measure: eob_total_other_pr {
    label: "Total OPR"
    type: sum
    sql: ${TABLE}.eob_total_other_pr ;;
  }

  measure: eob_avg_other_pr {
    label: "Average Other PR"
    type: average
    sql: ${TABLE}.eob_total_other_pr ;;
  }

  measure: eob_total_real_pr {
    label: "Total Real PR"
    type: sum
    sql: ${TABLE}.eob_total_real_pr ;;
  }

  measure: eob_total_pr {
    label: "Average PR from ERA"
    type: average
    sql: ${TABLE}.eob_total_real_pr + ${TABLE}.eob_total_other_pr ;;
  }

  measure: eob_avg_real_pr {
    label: "Average Real PR"
    type: average
    sql: ${TABLE}.eob_total_real_pr ;;
  }

  measure: eob_total_charged {
    label: "Total EOB Charged"
    type: sum
    sql: ${TABLE}.eob_total_charged ;;
  }

  measure: eob_total_allowed {
    label: "Total Allowed"
    type: sum
    hidden: yes
    sql: ${TABLE}.eob_total_allowed ;;
  }

  measure: eob_total_denied {
    label: "Total Denied"
    type: sum
    sql: ${TABLE}.eob_total_denial_adjustments ;;
  }

  measure: eob_total_opr_or_denied {
    label: "Total Denied or OPR"
    type: number
    value_format: "$#,###"
    drill_fields: [detail]
    sql: coalesce(${eob_total_denied},0) + coalesce(${eob_total_other_pr},0) ;;
  }
}

eob_summary.view.lookml · 108 lines

source-preserved

- view: eob_summary
  derived_table:
    sql: SELECT
  eob_total_charged
  , eob_total_paid
  , eob_total_allowed
  , eob_total_real_pr
  , eob_total_other_pr
  , eob_count
  , eob_total_denial_adjustments
  , claim_id
  , order_id
  , claim_total_charges
  , date_claim_received
  , date_processed_by_payer
  , first_eob_recorded_date
  , latest_eob_recorded_date
  , CASE WHEN claim_total_charges = eob_total_allowed THEN 'Fully-Paid'
    WHEN claim_total_charges = eob_total_other_pr THEN 'Full-OPR'
    WHEN claim_total_charges != eob_total_allowed AND eob_total_allowed > 0  THEN 'Partially-Paid'
    WHEN eob_total_allowed = 0 THEN 'Denied' ELSE 'Other Adj Type' END AS paid_state
FROM ${eob_summary_intermediate.SQL_TABLE_NAME} AS es
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
  fields:
  - dimension: claim_id
    type: int
    hidden: true
    sql: ${TABLE}.claim_id
  - dimension: order_id
    type: int
    primary_key: true
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: paid_state
    hidden: true
    label: Paid/Denied State
    sql: ${TABLE}.paid_state
  - dimension: date_claim_received
    type: date
    sql: ${TABLE}.date_claim_received
  - dimension: date_processed_by_payer
    hidden: true
    sql: ${TABLE}.date_processed_by_payer
  - dimension: first_eob_recorded_date
    type: time
    sql: ${TABLE}.first_eob_recorded_date
  - dimension: latest_eob_recorded_date
    type: time
    sql: ${TABLE}.latest_eob_recorded_date
  - dimension: is_latest_eob_recorded
    type: yesno
    sql: ${eob.date_recorded_time} = ${latest_eob_recorded_date_time}
  - measure: eob_count
    type: sum
    label: EOB Count
    sql: ${TABLE}.eob_count
  - measure: eob_total_paid
    type: sum
    label: Total Payer Paid
    value_format: '$#,###'
    sql: ${TABLE}.eob_total_paid
  - measure: absolute_value_total_amount
    type: sum
    label: Payer Refund Total Amount
    value_format: '$#,###'
    drill_fields:
    - detail2*
    sql: CASE WHEN ${TABLE}.eob_total_paid<0 THEN ABS(${TABLE}.eob_total_paid) ELSE NULL END
  - measure: eob_total_other_pr
    type: sum
    label: Total OPR
    sql: ${TABLE}.eob_total_other_pr
  - measure: eob_avg_other_pr
    type: average
    label: Average Other PR
    sql: ${TABLE}.eob_total_other_pr
  - measure: eob_total_real_pr
    type: sum
    label: Total Real PR
    sql: ${TABLE}.eob_total_real_pr
  - measure: eob_total_pr
    type: average
    label: Average PR from ERA
    sql: ${TABLE}.eob_total_real_pr + ${TABLE}.eob_total_other_pr
  - measure: eob_avg_real_pr
    type: average
    label: Average Real PR
    sql: ${TABLE}.eob_total_real_pr
  - measure: eob_total_charged
    type: sum
    label: Total EOB Charged
    sql: ${TABLE}.eob_total_charged
  - measure: eob_total_allowed
    type: sum
    label: Total Allowed
    sql: ${TABLE}.eob_total_allowed
  - measure: eob_total_denied
    type: sum
    label: Total Denied
    sql: ${TABLE}.eob_total_denial_adjustments
  - measure: eob_total_opr_or_denied
    type: number
    label: Total Denied or OPR
    value_format: '$#,###'
    drill_fields:
    - detail
    sql: coalesce(${eob_total_denied},0) + coalesce(${eob_total_other_pr},0)

eob_summary.sql
eob_summary.sql
eob_summary.view.lkml
eob_summary.view.lookml

Eob Summary

Blocks

Assumptions

Contents