Invoice

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

SELECT
  id
  , amount
  , invoice_number
  , sent
  , status
  , timestamp
  , type
  , CASE WHEN type = 'Customer' THEN amount - coalesce(mi.moop_amount,0) ELSE Null END as patient_inv_amt
  , mi.moop_amount as moop_comps
FROM  ${SCHEMA}.invoice
LEFT JOIN {{ ref('moop_by_invoice_id') }} as mi on mi.invoice_id = invoice.id

# models/schema.yml
version: 2

models:
  - name: invoice
    columns:
      - name: id
        tests:
          - unique
          - not_null
      - name: amount
      - name: invoice_number
      - name: status
      - name: type
      - name: patient_inv_amt
      - name: patient_inv_amt_tier
      - name: patient_inv_amt_tier_no_comps

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.

SELECT
  id
  , amount
  , invoice_number
  , sent
  , status
  , timestamp
  , type
  , CASE WHEN type = 'Customer' THEN amount - coalesce(mi.moop_amount,0) ELSE Null END as patient_inv_amt
  , mi.moop_amount as moop_comps
FROM  ${SCHEMA}.invoice
LEFT JOIN ${moop_by_invoice_id.SQL_TABLE_NAME} as mi on mi.invoice_id = invoice.id

view: invoice {
  derived_table: {
    sql: SELECT
  id
  , amount
  , invoice_number
  , sent
  , status
  , timestamp
  , type
  , CASE WHEN type = 'Customer' THEN amount - coalesce(mi.moop_amount,0) ELSE Null END as patient_inv_amt
  , mi.moop_amount as moop_comps
FROM  ${SCHEMA}.invoice
LEFT JOIN ${moop_by_invoice_id.SQL_TABLE_NAME} as mi on mi.invoice_id = invoice.id ;;
  }

  dimension: id {
    label: "Invoice ID"
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

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

  dimension: invoice_number {
    sql: ${TABLE}.invoice_number ;;
  }

  dimension: status {
    label: "Invoice Status"
    sql: ${TABLE}.status ;;
  }

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

  dimension: patient_inv_amt {
    label: "Average Patient Invoice Amount"
    type: number
    value_format: "$###"
    sql: ${TABLE}.patient_inv_amt ;;
  }

  dimension: patient_inv_amt_tier {
    label: "Patient Invoice Tier After Comps"
    type: tier
    sql: ${TABLE}.patient_inv_amt ;;
  }

  dimension: patient_inv_amt_tier_no_comps {
    label: "Patient Invoice Tier No Comps"
    type: tier
    sql: ${amount} ;;
  }

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

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

  measure: avg_patient_inv_amt {
    label: "Average Patient Invoice Amount"
    type: average
    value_format: "$###"
    sql: ${TABLE}.patient_inv_amt ;;
  }

  measure: count {
    label: "Invoice Count"
    type: count
    drill_fields: [id, invoiceitem.count, payment.count]
  }

  measure: amount_total {
    type: sum
    sql: ${amount} ;;
  }

  measure: amount_total_insurance {
    type: sum
    filters: [type: "Insurer"]
    sql: ${amount} ;;
  }

  measure: amount_consignment_total {
    type: sum
    sql: ${amount} ;;
  }

  measure: customer_invoice_amount {
    type: sum
    hidden: yes
    sql: ${amount} ;;
  }

  measure: consignment_invoice_amount_remaining {
    type: number
    sql: ${amount_total} - ${aggregated_payments.total_comps}-${aggregated_payments.Payment_Amount} ;;
  }
}

- view: invoice
  derived_table:
    sql: SELECT
  id
  , amount
  , invoice_number
  , sent
  , status
  , timestamp
  , type
  , CASE WHEN type = 'Customer' THEN amount - coalesce(mi.moop_amount,0) ELSE Null END as patient_inv_amt
  , mi.moop_amount as moop_comps
FROM  ${SCHEMA}.invoice
LEFT JOIN ${moop_by_invoice_id.SQL_TABLE_NAME} as mi on mi.invoice_id = invoice.id
  fields:
  - dimension: id
    type: int
    primary_key: true
    label: Invoice ID
    sql: ${TABLE}.id
  - dimension: amount
    type: number
    hidden: true
    sql: ${TABLE}.amount
  - dimension: invoice_number
    sql: ${TABLE}.invoice_number
  - dimension: status
    label: Invoice Status
    sql: ${TABLE}.status
  - dimension: type
    sql: ${TABLE}.type
  - dimension: patient_inv_amt
    type: number
    label: Average Patient Invoice Amount
    value_format: '$###'
    sql: ${TABLE}.patient_inv_amt
  - dimension: patient_inv_amt_tier
    type: tier
    label: Patient Invoice Tier After Comps
    sql: ${TABLE}.patient_inv_amt
  - dimension: patient_inv_amt_tier_no_comps
    type: tier
    label: Patient Invoice Tier No Comps
    sql: ${amount}
  - dimension_group: sent
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.sent
  - dimension_group: timestamp
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - year
    sql: ${TABLE}.timestamp
  - measure: avg_patient_inv_amt
    type: average
    label: Average Patient Invoice Amount
    value_format: '$###'
    sql: ${TABLE}.patient_inv_amt
  - measure: count
    type: count
    label: Invoice Count
    drill_fields:
    - id
    - invoiceitem.count
    - payment.count
  - measure: amount_total
    type: sum
    sql: ${amount}
  - measure: amount_total_insurance
    type: sum
    filters:
    - type: Insurer
    sql: ${amount}
  - measure: amount_consignment_total
    type: sum
    sql: ${amount}
  - measure: customer_invoice_amount
    type: sum
    sql: ${amount}
  - measure: consignment_invoice_amount_remaining
    type: number
    sql: ${amount_total} - ${aggregated_payments.total_comps}-${aggregated_payments.Payment_Amount}