# View: nr_queries_graphs

**View Name:** nr_queries_graphs
**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_graphs.view.lkml`

## Overview

- **File Size:** 7535 bytes
- **Lines of Code:** 224
- **Dimensions:** 20
- **Measures:** 1
- **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 |
| `Year` | date_year |
| `Month` | date_month |
| `Month_Num` | date_month_num |

## Measures

| Name | Type |
|------|------|
| `Count` | count |

## 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;;
```