#this view derives the deletion date by using the vendor_notes table and finding the date that the label offically had a status of the note_text below

view: deleted_labels_snowflake_vn {
  derived_table: {
    sql: select
        v.vendor_id,
        n.note_date,
        n.note_text
      from  royalty_accounting_reporting.prod.vw_dim_abacus_ar_vendor v
      left join orchard_app_reporting_v2.art_relations_prod_art_relations.vendor_notes vn on v.vendor_id=vn.vendor_id
      left join orchard_app_reporting_v2.art_relations_prod_art_relations.note n on vn.note_id = n.note_id
      where n.note_text = 'Label status changed from signed to deletion.'
      group by 1,2,3
       ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  dimension: vendor_id {
    type: number
    label: "Deleted Vendor IDs (By Official Status Change)"
    description: "This deletion date is derived by taking the date that a Client Manager/ Internal Orchard employee changed the Label status from signed to deleted. This field should be used for legal purposes"
    sql: ${TABLE}."VENDOR_ID" ;;
  }

  dimension_group: note_date {
    type: time
    label: "Date Label Deleted (By Official Status Change)"
    description: "This deletion date is derived by taking the date that a Client Manager/ Internal Orchard employee changed the Label status from signed to deleted. This field should be used for legal purposes"
    timeframes: [date, month, week, year, quarter, time]
   sql: ${TABLE}."NOTE_DATE" ;;
  }

  dimension: note_text {
    type: string
    hidden: yes
    sql: ${TABLE}."NOTE_TEXT" ;;
  }

  measure: count_deleted{
    type: count_distinct
    label: "Number of Deleted Labels (By Official Status Change)"
    description: "This field counts the number of labels deleted using the deletion date that is derived by taking the date that a Client Manager/ Internal Orchard employee changed the Label status from signed to deleted. This field should be used for legal purposes"
    sql: ${TABLE}.vendor_id ;;
  }

  set: detail {
    fields: [vendor_id, note_date_time, note_text]
  }
}