Court Case No Duplicates

view: court_case_no_duplicates {
  sql_table_name: poc_db.PUBLIC.COURT_CASE_NO_DUPLICATES ;;

  dimension: caseid {
    primary_key: yes
    sql: ${TABLE}.CASEID ;;
  }

  dimension: casenumber {
    sql: ${TABLE}.CASENUMBER ;;
  }

  dimension: casepartyid {
    sql: ${TABLE}.CASEPARTYID ;;
  }

  dimension: casestatus {
    sql: ${TABLE}.CASESTATUS ;;
  }

  dimension: casestatuscode {
    sql: ${TABLE}.CASESTATUSCODE ;;
  }

  dimension: casestatuscodeid {
    sql: ${TABLE}.CASESTATUSCODEID ;;
  }

  dimension: casetitle {
    sql: ${TABLE}.CASETITLE ;;
  }

  dimension: casetype {
    sql: ${TABLE}.CASETYPE ;;
  }

  dimension: casetypecode {
    sql: ${TABLE}.CASETYPECODE ;;
  }

  dimension: casetypecodeid {
    sql: ${TABLE}.CASETYPECODEID ;;
  }

  dimension: courtnumber {
    sql: ${TABLE}.COURTNUMBER ;;
  }

  dimension: defendantname {
    sql: ${TABLE}.DEFENDANTNAME ;;
  }

  dimension: ethnicity {
    sql: CASE
WHEN (${TABLE}.ethnicity = 'Hispanic') THEN 'Hispanic'
WHEN (${TABLE}.ethnicity = 'Non Hispanic') THEN 'Non-Hispanic'
ELSE 'Unavailable'
END ;;
  }

  dimension: gender {
    sql: CASE
WHEN (${TABLE}.gender = 'Male') THEN 'Male'
WHEN (${TABLE}.gender = 'Female') THEN 'Female'
ELSE 'Unavailable'
END ;;
  }

  dimension: language {
    sql: ${TABLE}.LANGUAGE ;;
  }

  dimension: lastfourssn {
    sql: ${TABLE}.LASTFOURSSN ;;
  }

  dimension: namefirst {
    sql: ${TABLE}.NAMEFIRST ;;
  }

  dimension: namelast {
    sql: ${TABLE}.NAMELAST ;;
  }

  dimension: namemid {
    sql: ${TABLE}.NAMEMID ;;
  }

  dimension: namesuffix {
    sql: ${TABLE}.NAMESUFFIX ;;
  }

  dimension: nametypeky {
    sql: ${TABLE}.NAMETYPEKY ;;
  }

  dimension: partyid {
    sql: ${TABLE}.PARTYID ;;
  }

  dimension: race {
    sql: CASE
WHEN (${TABLE}.race = 'Multir' or ${TABLE}.race = 'Multiracial') THEN 'Multiracial'
WHEN (${TABLE}.race = 'Black') THEN 'Black'
WHEN (${TABLE}.race = 'White') THEN 'White'
WHEN (${TABLE}.race = 'Indian' or ${TABLE}.race = 'Native') THEN 'Native American'
WHEN (${TABLE}.race = 'Asian') THEN 'Asian'
WHEN (${TABLE}.race = 'Native Hawa' or ${TABLE}.race = 'Native Hawaiian or Other Pacific Islander') THEN 'Native Hawaiian or Other Pacific Islander'
WHEN (${TABLE}.race = 'Other') THEN 'Other'
ELSE 'Unavailable' --put all the rest in "Unavailable"
END ;;
  }

  dimension_group: casefileddate {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.CASEFILEDDATE ;;
  }

  dimension_group: defendantdob {
    type: time
    timeframes: [date, week, month]
    convert_tz: no
    sql: ${TABLE}.DEFENDANTDOB ;;
  }

  measure: count {
    type: count
    drill_fields: [defendantname]
  }
}

- view: court_case_no_duplicates
  sql_table_name: poc_db.PUBLIC.COURT_CASE_NO_DUPLICATES
  fields:
  - dimension: caseid
    primary_key: true
    sql: ${TABLE}.CASEID
  - dimension: casenumber
    sql: ${TABLE}.CASENUMBER
  - dimension: casepartyid
    sql: ${TABLE}.CASEPARTYID
  - dimension: casestatus
    sql: ${TABLE}.CASESTATUS
  - dimension: casestatuscode
    sql: ${TABLE}.CASESTATUSCODE
  - dimension: casestatuscodeid
    sql: ${TABLE}.CASESTATUSCODEID
  - dimension: casetitle
    sql: ${TABLE}.CASETITLE
  - dimension: casetype
    sql: ${TABLE}.CASETYPE
  - dimension: casetypecode
    sql: ${TABLE}.CASETYPECODE
  - dimension: casetypecodeid
    sql: ${TABLE}.CASETYPECODEID
  - dimension: courtnumber
    sql: ${TABLE}.COURTNUMBER
  - dimension: defendantname
    sql: ${TABLE}.DEFENDANTNAME
  - dimension: ethnicity
    sql: CASE
WHEN (${TABLE}.ethnicity = 'Hispanic') THEN 'Hispanic'
WHEN (${TABLE}.ethnicity = 'Non Hispanic') THEN 'Non-Hispanic'
ELSE 'Unavailable'
END
  - dimension: gender
    sql: CASE
WHEN (${TABLE}.gender = 'Male') THEN 'Male'
WHEN (${TABLE}.gender = 'Female') THEN 'Female'
ELSE 'Unavailable'
END
  - dimension: language
    sql: ${TABLE}.LANGUAGE
  - dimension: lastfourssn
    sql: ${TABLE}.LASTFOURSSN
  - dimension: namefirst
    sql: ${TABLE}.NAMEFIRST
  - dimension: namelast
    sql: ${TABLE}.NAMELAST
  - dimension: namemid
    sql: ${TABLE}.NAMEMID
  - dimension: namesuffix
    sql: ${TABLE}.NAMESUFFIX
  - dimension: nametypeky
    sql: ${TABLE}.NAMETYPEKY
  - dimension: partyid
    sql: ${TABLE}.PARTYID
  - dimension: race
    sql: CASE
WHEN (${TABLE}.race = 'Multir' or ${TABLE}.race = 'Multiracial') THEN 'Multiracial'
WHEN (${TABLE}.race = 'Black') THEN 'Black'
WHEN (${TABLE}.race = 'White') THEN 'White'
WHEN (${TABLE}.race = 'Indian' or ${TABLE}.race = 'Native') THEN 'Native American'
WHEN (${TABLE}.race = 'Asian') THEN 'Asian'
WHEN (${TABLE}.race = 'Native Hawa' or ${TABLE}.race = 'Native Hawaiian or Other Pacific Islander') THEN 'Native Hawaiian or Other Pacific Islander'
WHEN (${TABLE}.race = 'Other') THEN 'Other'
ELSE 'Unavailable' --put all the rest in "Unavailable"
END
  - dimension_group: casefileddate
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.CASEFILEDDATE
  - dimension_group: defendantdob
    type: time
    timeframes:
    - date
    - week
    - month
    convert_tz: false
    sql: ${TABLE}.DEFENDANTDOB
  - measure: count
    type: count
    drill_fields:
    - defendantname