# View: dt_store_availability_analytics_reduced

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

## Overview

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

## Comments & Notes

- 72c275; background-color: #72c275; font-size:100%;">""</p>
- f4ea56; background-color: #f4ea56; font-size:100%;">""</p>
- ea9999; background-color: #ea9999; font-size:100%;">""</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(monthname(to_date(MISSING_DATE)) || ' ' || ltrim(RIGHT(MISSING_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY MISSING_DATE) AS MISSING_DATES,
        LISTAGG(monthname(to_date(INCOMPLETE_DATE)) || ' ' || ltrim(RIGHT(INCOMPLETE_DATE, 2), '0'), ', ') WITHIN GROUP (ORDER BY INCOMPLETE_DATE) AS INCOMPLETE_DATES,
        LISTAGG(monthname(to_date(DUPLICATED_DATE)) || ' ' || ltrim(RIGHT(DUPLICATED_DATE, 2), '0'), ', ') 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 || ';\n'
        ELSE '' END ||
      CASE WHEN INCOMPLETE_DATES  != ''
        THEN INCOMPLETE_DATES || ';\n'
        ELSE '' END ||
      CASE WHEN DUPLICATED_DATES != ''
        THEN DUPLICATED_DATES || ';\n'
        ELSE '' END || '\n'
      AS ERROR_DATES,
      STORE_HIGH_WATERMARK AS MAX_COMPLETE_DATE,
      current_date - MAX_COMPLETE_DATE as LATE_DELIVERY_DAYE,
      case
      when SA.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE <= 2 then 'green'
      when SA.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE <= 3 then 'red'
      when SA.FEEDID in (1, 4, 35, 36, 37) and LATE_DELIVERY_DAYE > 3 then 'red-notify'
      when SA.FEEDID in (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and LATE_DELIVERY_DAYE <= 2
      then 'green'
      when SA.FEEDID in (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and LATE_DELIVERY_DAYE = 3
      then 'yellow'
      when SA.FEEDID in (3, 9, 11, 13, 16, 38, 42, 43, 44, 49, 50, 51) and LATE_DELIVERY_DAYE > 3
      then 'red'
      end as STATUS
    FROM STORES_AGGREGATED SA
    LEFT JOIN FACTS.PROD.DIM_FEED DF ON SA.FEEDID = DF.FEEDID
    ORDER BY 1
;;
```