# View: nr_dupes

**View Name:** nr_dupes
**Table Source:** `Derived Table (Custom SQL)`
**File Path:** `views/nr_dupes.view.lkml`

## Overview

- **File Size:** 2797 bytes
- **Lines of Code:** 104
- **Dimensions:** 14
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `Dupe_ID` | string |
| `Owner` | string |
| `Society` | string |
| `Client_AGR` | string |
| `Other_Claimant_ID` | string |
| `Other_Claimant` | string |
| `Contributor` | string |
| `Service_Level` | string |
| `Created_Date` | date |
| `Closed_Date` | date |
| `Past_Review` | yesno |
| `Review_Date` | date |
| `Last_History` | date |
| `Status` | string |

## Derived Table

```sql
sql:
    SELECT
      d.id dupe_id,
      society.name source,
      d.entry_date,
      d.review_date,
      current_date today,
      d.status status,
      d.close_date,
      owner.name owner,
      agr.id agreement_id,
      agn.name assignor,
      claimant.id claimant_id,
      claimant.name claimant,
      ctr.id as contributor_id,
      ctr.name as contributor,
      case when (agr.task_service_level = 4) then 'Platinum' when (agr.task_service_level = 3) then 'Gold' when (agr.task_service_level = 2) then 'Silver' when (agr.task_service_level = 1) then 'Bronze' else null end as service_level,
      max(his.created_date) last_history_date,
      case when current_date > d.review_date then 'Yes' else 'No' end as past_review
      FROM
        (((((("AWAL_SAX"."SAX"."NR_DUPES" d
        INNER JOIN "AWAL_SAX"."SAX"."INTERESTED_PARTIES" society ON (d.society_id = society.id))
        LEFT JOIN "AWAL_SAX"."SAX"."AGREEMENTS" agr ON (d.dupe_agreement_id = agr.id))
        INNER JOIN "AWAL_SAX"."SAX"."INTERESTED_PARTIES" agn ON (agr.assignor_id = agn.id))
         INNER JOIN "AWAL_SAX"."SAX"."INTERESTED_PARTIES" claimant ON (d.other_claimant_id = claimant.id))
        INNER JOIN "AWAL_SAX"."SAX"."SAX_USERS" owner ON (d.owner_user_id = owner.id))
        LEFT JOIN "AWAL_SAX"."SAX"."NR_DUPE_HISTORY" his ON (his.dupe_id = d.id))
        LEFT JOIN "AWAL_SAX"."SAX"."INTERESTED_PARTIES" ctr on ctr.id = d.contributor_id
    GROUP BY
  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15;;
```