Daily Summary

view: daily_summary {
  sql_table_name: DAILY_SUMMARY ;;

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

  dimension: product {
    sql: CASE WHEN charge_type = 1 OR charge_type = 11 THEN 'Credit Check'
WHEN charge_type = 2 THEN 'Landlord Tenant Court Search'
WHEN charge_type = 3 THEN 'Criminal Search'
WHEN charge_type = 5 THEN 'Verification'
WHEN charge_type = 6 THEN 'Printed Lease'
WHEN charge_type = 7 THEN 'Service Service Charge'
WHEN charge_type = 8 THEN 'Discretionary Charge'
WHEN charge_type = 9 THEN 'Address Update'
WHEN charge_type = 10 THEN 'Insurance Rebate'
WHEN charge_type = 12 THEN 'Credit Card Payment'
WHEN charge_type = 13 THEN 'Processing Fee'
WHEN charge_type = 14 THEN 'Sales Tax'
WHEN charge_type = 15 THEN 'Property Inspection'
WHEN charge_type = 16 THEN 'FACTA Surcharge'
WHEN charge_type = 17 THEN 'Collection Submission'
WHEN charge_type = 18 THEN 'Collection Commission'
WHEN charge_type = 19 THEN 'Legal Notices Service Fee'
WHEN charge_type = 20 THEN 'Rental History Search'
WHEN charge_type = 21 THEN 'Touch Tone Offset'
WHEN charge_type = 22 THEN 'Property Website'
WHEN charge_type = 23 THEN 'Social Search'
WHEN charge_type = 24 THEN 'Direct Debit Payment'
WHEN charge_type = 25 THEN 'Property Website Setup Fee'
WHEN charge_type = 26 THEN 'Corporate Credit Check'
WHEN charge_type = 27 THEN 'Property Website Photography Fee'
WHEN charge_type = 28 THEN 'Corporate Website Setup Fee'
WHEN charge_type = 29 THEN 'Corporate Website'
WHEN charge_type = 30 THEN 'Ad Blast'
WHEN charge_type = 31 THEN 'Resident Services'
WHEN charge_type = 32 THEN 'Leasing Center Setup Fee'
WHEN charge_type = 33 THEN 'Leasing Center'
WHEN charge_type = 34 THEN 'E-Signatures'
WHEN charge_type = 35 THEN 'Retroactive Sales Tax Assessment'
WHEN charge_type = 36 THEN 'Marketing Fee'
WHEN charge_type = 37 THEN 'Certified Documents'
WHEN charge_type = 38 THEN 'Rental Address Services'
WHEN charge_type = 39 THEN 'CloudFile'
WHEN charge_type = 40 THEN 'Hosting'
WHEN charge_type = 41 THEN 'Leads'
WHEN charge_type = 42 THEN 'Unit Availability'
WHEN charge_type = 43 THEN 'Online Payment Fee'
WHEN charge_type = 44 THEN 'Online Leasing, Renewals'
WHEN charge_type = 45 THEN 'OFAC SDN Search'
ELSE 'Unknown' END ;;
  }

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

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

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

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

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

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

  dimension: commissionable {
    sql: CASE WHEN ${charge_type} IN (8,12,13,14,15,16,17,18,24,25,27,28,32,35,36) THEN 0 ELSE 1 END ;;
  }

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

  measure: billing_amount {
    type: sum
    sql: ${total_dim} ;;
  }

  measure: num_records {
    type: sum
    sql: ${num_records_dim} ;;
  }

  measure: count {
    type: count
  }

  measure: product_list {
    type: list
  }
}

- view: daily_summary
  sql_table_name: DAILY_SUMMARY
  fields:
  - dimension: charge_type
    type: int
    hidden: true
    sql: ${TABLE}.charge_type
  - dimension: product
    sql: CASE WHEN charge_type = 1 OR charge_type = 11 THEN 'Credit Check'
WHEN charge_type = 2 THEN 'Landlord Tenant Court Search'
WHEN charge_type = 3 THEN 'Criminal Search'
WHEN charge_type = 5 THEN 'Verification'
WHEN charge_type = 6 THEN 'Printed Lease'
WHEN charge_type = 7 THEN 'Service Service Charge'
WHEN charge_type = 8 THEN 'Discretionary Charge'
WHEN charge_type = 9 THEN 'Address Update'
WHEN charge_type = 10 THEN 'Insurance Rebate'
WHEN charge_type = 12 THEN 'Credit Card Payment'
WHEN charge_type = 13 THEN 'Processing Fee'
WHEN charge_type = 14 THEN 'Sales Tax'
WHEN charge_type = 15 THEN 'Property Inspection'
WHEN charge_type = 16 THEN 'FACTA Surcharge'
WHEN charge_type = 17 THEN 'Collection Submission'
WHEN charge_type = 18 THEN 'Collection Commission'
WHEN charge_type = 19 THEN 'Legal Notices Service Fee'
WHEN charge_type = 20 THEN 'Rental History Search'
WHEN charge_type = 21 THEN 'Touch Tone Offset'
WHEN charge_type = 22 THEN 'Property Website'
WHEN charge_type = 23 THEN 'Social Search'
WHEN charge_type = 24 THEN 'Direct Debit Payment'
WHEN charge_type = 25 THEN 'Property Website Setup Fee'
WHEN charge_type = 26 THEN 'Corporate Credit Check'
WHEN charge_type = 27 THEN 'Property Website Photography Fee'
WHEN charge_type = 28 THEN 'Corporate Website Setup Fee'
WHEN charge_type = 29 THEN 'Corporate Website'
WHEN charge_type = 30 THEN 'Ad Blast'
WHEN charge_type = 31 THEN 'Resident Services'
WHEN charge_type = 32 THEN 'Leasing Center Setup Fee'
WHEN charge_type = 33 THEN 'Leasing Center'
WHEN charge_type = 34 THEN 'E-Signatures'
WHEN charge_type = 35 THEN 'Retroactive Sales Tax Assessment'
WHEN charge_type = 36 THEN 'Marketing Fee'
WHEN charge_type = 37 THEN 'Certified Documents'
WHEN charge_type = 38 THEN 'Rental Address Services'
WHEN charge_type = 39 THEN 'CloudFile'
WHEN charge_type = 40 THEN 'Hosting'
WHEN charge_type = 41 THEN 'Leads'
WHEN charge_type = 42 THEN 'Unit Availability'
WHEN charge_type = 43 THEN 'Online Payment Fee'
WHEN charge_type = 44 THEN 'Online Leasing, Renewals'
WHEN charge_type = 45 THEN 'OFAC SDN Search'
ELSE 'Unknown' END
  - dimension: company_id
    type: int
    sql: ${TABLE}.company_id
  - dimension: invoice_id
    type: int
    hidden: true
    sql: ${TABLE}.invoice_id
  - dimension: num_records_dim
    type: int
    hidden: true
    sql: ${TABLE}.num_records
  - dimension: property_id
    type: int
    sql: ${TABLE}.property_id
  - dimension: sales_tax_location_code
    sql: ${TABLE}.sales_tax_location_code
  - dimension: total_dim
    type: number
    hidden: true
    sql: ${TABLE}.total
  - dimension: commissionable
    sql: CASE WHEN ${charge_type} IN (8,12,13,14,15,16,17,18,24,25,27,28,32,35,36) THEN 0 ELSE 1 END
  - dimension_group: billing
    type: time
    timeframes:
    - date
    - week
    - month
    - year
    sql: ${TABLE}.date
  - measure: billing_amount
    type: sum
    sql: ${total_dim}
  - measure: num_records
    type: sum
    sql: ${num_records_dim}
  - measure: count
    type: count
  - measure: product_list
    type: list