# View: nr_queries

**View Name:** nr_queries
**Table Source:** `Derived from: Awal_Sax, Awal_Sax.Sax, Sax.Agreement_Outgoing_Types, Sax.Agreement_Period_Length_Defs, Sax.Agreement_Periods + 1 more`
**File Path:** `views/nr_queries.view.lkml`

## Overview

- **File Size:** 7025 bytes
- **Lines of Code:** 205
- **Dimensions:** 17
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Dimensions

| Name | Type |
|------|------|
| `NR_Query_ID` | string |
| `Created_By` | string |
| `Owner` | string |
| `Query_Title` | string |
| `Client_Name` | string |
| `Society` | string |
| `Query_Type` | string |
| `Query_Sub_Type` | string |
| `AGR_IDs` | string |
| `Service_Level` | string |
| `Created_Date` | date |
| `Closed_Date` | date |
| `Review_Date` | date |
| `Past_Review` | yesno |
| `Last_History` | date |
| `Last_History_By` | string |
| `Status` | string |

## SQL Comments

- For old agreements with non-standard set up
- client agreements

## Derived Table

```sql
sql:
  WITH CLIENTS AS (          SELECT   IP.ID CONTR_ID,
                                    IP.NAME CONTR_NAME,
                                    NVL(IP.GROUP_NAME, IP.PERFORMER_NAME) GROUP_NAME,
                                    A.ID CONTR_AGR_ID,
                                    CONNECT_BY_ROOT IP.ID CLIENT_ID,
                                    CONNECT_BY_ROOT IP.NAME CLIENT_NAME,
                                    CONNECT_BY_ROOT A.ID CLIENT_AGR_ID,
                                    CONNECT_BY_ROOT A.TERRITORY_SUMMARY CLIENT_TERR_SUM,
                                    CONNECT_BY_ROOT A.PRIMARY_ADMIN_OWNER_ID PRIMARY_ADMIN_OWNER_ID,
                                    CONNECT_BY_ROOT A.SECONDARY_ADMIN_OWNER_ID SECONDARY_ADMIN_OWNER_ID,
                                    TO_CHAR(AP.START_DATE, 'DD/MM/YYYY') AP_START_DATE,
                                    CONNECT_BY_ROOT A.TASK_SERVICE_LEVEL SERVICE_LEVEL
                               FROM awal_sax.sax.AGREEMENTS A
                         INNER JOIN awal_sax.sax.INTERESTED_PARTIES IP ON A.ASSIGNOR_ID = IP.ID
                    LEFT OUTER JOIN awal_sax.sax.AGREEMENT_PERIODS AP
                                 ON AP.AGREEMENT_ID = A.ID
                                AND PERIOD_LENGTH_DEFINITION_ID IN (SELECT ID FROM awal_sax.sax.AGREEMENT_PERIOD_LENGTH_DEFS WHERE CODE = 'FIXED_DATES')
                              WHERE IP.IP_TYPE = 'Person'
                                /*AND DECODE(IP.ID,                  -- For old agreements with non-standard set up
                                           'IP98853', 'AGR316757',
                                           'IP98854', 'AGR316737',
                                           'IP300576', 'AGR293783',
                                           'IP300577', 'AGR296337',
                                           'IP210353', 'AGR296220',
                                           'IP246238', 'AGR297039',
                                           'IP71436', 'AGR225801',
                                           'IP72396', 'AGR225806',
                                           'IP72394', 'AGR225804',
                                           'IP71507', 'AGR225802',
                                           'IP58428', 'AGR225805',
                                           CONNECT_BY_ROOT A.ID) = CONNECT_BY_ROOT A.ID*/
                         START WITH A.ID IN (SELECT A.ID
                                               FROM awal_sax.sax.AGREEMENTS A
                                         INNER JOIN awal_sax.sax.AGREEMENT_OUTGOING_TYPES AOT ON A.AGREEMENT_OUTGOING_TYPE_ID = AOT.ID
                                              WHERE AOT.CODE = 'CLIENT'
                                                AND A.CLIENT_RIGHTS = 'Neighbouring Rights') -- client agreements
                   CONNECT BY PRIOR A.ASSIGNOR_ID = A.ACQUIRER_ID
                                AND A.AGREEMENT_TYPE_ID IS NULL
                                AND A.USE_FOR_NEIGHBOURING = 'Y')

select
      q.id as query_id,
      owner.name as owner_name,
      society.name as society_name,
      q.query_title as query_title,
      q.created_by as query_created_by,
      q.query_type as type,
      st.description as sub_type,
      array_to_string(array_agg(agr.id), ', ') as agr_id,
      array_to_string(array_agg(agn.name), ', ') as client_name,
      a.agr_sl as agr_sl,
      ctr.name as contr_name,
      q.created_date as created_date,
      q.closed_date as closed_date,
      q.review_date as review_date,
      case when q.status = 'Closed' then 'Closed' else
      (case when current_date > q.review_date then 'Yes' else 'No' end) end as past_review,
      max.created_date as latest_history,
      max.created_by as latest_history_by,
      q.status as status
from
      "AWAL_SAX"."SAX"."NR_QUERIES" q
left join
      "AWAL_SAX"."SAX"."NR_QUERY_AGREEMENTS" qa
      on qa.nr_query_id = q.id
left join
      "AWAL_SAX"."SAX"."NR_QUERY_SUB_TYPES" st
      on st.id = q.query_sub_type_id
left join
      "AWAL_SAX"."SAX"."AGREEMENTS" agr
      on agr.id = qa.agreement_id
left join
      (select qa.nr_query_id as query_id,
              array_to_string(array_agg(distinct clients.service_level), ', ') as agr_sl
              from "AWAL_SAX"."SAX"."NR_QUERY_AGREEMENTS" qa
              left join clients
              on clients.client_agr_id = qa.agreement_id
              group by qa.nr_query_id) a on a.query_id = q.id
left join
      (select his.nr_query_id as query_id,
              his.created_date as created_date,
              his.created_by as created_by,
              rank() over (partition by his.nr_query_id order by his.created_date desc) as rank
                                        from "AWAL_SAX"."SAX"."NR_QUERY_HISTORY" his) max on max.query_id = q.id
                                                                                and max.rank = 1
left join
      "AWAL_SAX"."SAX"."INTERESTED_PARTIES" agn
      on agn.id = agr.assignor_id
left join
      "AWAL_SAX"."SAX"."INTERESTED_PARTIES" ctr
      on ctr.id = q.contributor_id
left join
      "AWAL_SAX"."SAX"."INTERESTED_PARTIES" society
      on society.id = q.society_id
left join
      "AWAL_SAX"."SAX"."SAX_USERS" owner
      on owner.id = q.owner_id
 group by
      q.id,
      owner.name,
      society.name,
      q.query_title,
      q.created_by,
      q.query_type,
      st.description,
      a.agr_sl,
      ctr.name,
      q.created_date,
      q.closed_date,
      q.review_date,
      case when q.status = 'Closed' then 'Closed' else
      (case when current_date > q.review_date then 'Yes' else 'No' end) end,
      max.created_date,
      max.created_by,
      q.status;;
```