# View: dt_store_availability_analytics

**View Name:** dt_store_availability_analytics
**Table Source:** `Derived from: Filtered_Summary, Prod.Data_Availability_By_Store_Daily, Prod.Dim_Feed, Prod.Store_Slas, Store_Summary + 1 more`
**File Path:** `dt_store_availability_analytics.view.lkml`

## Overview

- **File Size:** 5340 bytes
- **Lines of Code:** 179
- **Dimensions:** 6
- **Measures:** 0
- **Dimension Groups:** 0
- **Filters:** 0

## Comments & Notes

- EC7063; font-size:100%;">{{ rendered_value }}</p>
- 58FA58; font-size:100%;">{{ rendered_value }}</p>

## Dimensions

| Name | Type |
|------|------|
| `feedid` | number |
| `feedname` | string |
| `error_dates` | string |
| `max_complete_date` | date |
| `overal_status` | string |
| `priority_ranking` | string |

## Derived Table

```sql
sql:
    WITH STORE_SUMMARY AS (
      SELECT
        AVAIL.FEEDID,
        CASE WHEN MISSING_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD, CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS MISSING_DATE,
        CASE WHEN INCOMPLETE_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD,  CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS INCOMPLETE_DATE,
        CASE WHEN DUPLICATED_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD,  CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS DUPLICATED_DATE,
        STORE_HIGH_WATERMARK
      FROM FACTS.PROD.DATA_AVAILABILITY_BY_STORE_DAILY AVAIL
      INNER JOIN INTELLIGENCE.PROD.STORE_SLAS SLAS ON AVAIL.FEEDID = SLAS.FEEDID
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL AND AVAIL.FEEDID != 44
      GROUP BY 1, 2, 3, 4, 5
      UNION ALL
      SELECT
        AVAIL.FEEDID,
        CASE WHEN MISSING_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE > '2021-08-15' AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD, CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS MISSING_DATE,
        CASE WHEN INCOMPLETE_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE > '2021-08-15' AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD,  CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS INCOMPLETE_DATE,
        CASE WHEN DUPLICATED_DATE IS NOT NULL AND DOWNLOAD_ACTIVITY_DATE > '2021-08-15' AND DOWNLOAD_ACTIVITY_DATE < DATEADD('DAY', -THRESHOLD,  CURRENT_DATE())
          THEN DOWNLOAD_ACTIVITY_DATE
          END AS DUPLICATED_DATE,
        STORE_HIGH_WATERMARK
      FROM FACTS.PROD.DATA_AVAILABILITY_BY_STORE_DAILY AVAIL
      INNER JOIN INTELLIGENCE.PROD.STORE_SLAS SLAS ON AVAIL.FEEDID = SLAS.FEEDID
      WHERE COALESCE(MISSING_DATE, INCOMPLETE_DATE, DUPLICATED_DATE) IS NOT NULL AND AVAIL.FEEDID = 44
      GROUP BY 1, 2, 3, 4, 5
    ),

    FILTERED_SUMMARY AS (
      SELECT *
        FROM STORE_SUMMARY
        WHERE MISSING_DATE > '2021-01-17' or missing_Date is null),


    STORES_AGGREGATED AS (
      SELECT
        FEEDID,
        LISTAGG(RIGHT(MISSING_DATE, 5), ', ') WITHIN GROUP (ORDER BY MISSING_DATE) AS MISSING_DATES,
        LISTAGG(RIGHT(INCOMPLETE_DATE, 5), ', ') WITHIN GROUP (ORDER BY INCOMPLETE_DATE) AS INCOMPLETE_DATES,
        LISTAGG(RIGHT(DUPLICATED_DATE, 5), ', ') WITHIN GROUP (ORDER BY DUPLICATED_DATE) AS DUPLICATED_DATES,
        STORE_HIGH_WATERMARK
      FROM FILTERED_SUMMARY
      GROUP BY 1, 5
    )

    SELECT
      SA.FEEDID,
      DF.FEEDNAME,
      '\n'||
      CASE WHEN MISSING_DATES != ''
        THEN '-Missing Dates: ' || MISSING_DATES || '\n'
        ELSE '' END ||
      CASE WHEN INCOMPLETE_DATES  != ''
        THEN '-Incomplete Dates: ' || INCOMPLETE_DATES || '\n'
        ELSE '' END ||
      CASE WHEN DUPLICATED_DATES != ''
        THEN '-Duplicated Dates: ' || DUPLICATED_DATES || '\n'
        ELSE '' END || '\n'
      AS ERROR_DATES,
      STORE_HIGH_WATERMARK AS MAX_COMPLETE_DATE
    FROM STORES_AGGREGATED SA
    LEFT JOIN FACTS.PROD.DIM_FEED DF ON SA.FEEDID = DF.FEEDID
    ORDER BY 1
;;
```