SELECT
  D.WEEK_ID AS labels,
  D.filter_value,
  D.filter_label,
  D.filter_color,
  D.alarm_value
FROM
  (SELECT
    A.WEEK_ID,
    F.filter_value,
    F.filter_label,
    F.filter_color,
    F.filter_order,
    IFNULL(C.total, 0) AS alarm_value
  FROM
    (SELECT
      CONVERT (
        DATE_FORMAT(
          STR_TO_DATE('2017-01-31', '%Y-%m-%d') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) WEEK,
          '%x-%v'
        ) USING utf8
      ) AS WEEK_ID
    FROM
      (SELECT
        0 AS a
      UNION
      ALL
      SELECT
        1
      UNION
      ALL
      SELECT
        2
      UNION
      ALL
      SELECT
        3
      UNION
      ALL
      SELECT
        4
      UNION
      ALL
      SELECT
        5
      UNION
      ALL
      SELECT
        6
      UNION
      ALL
      SELECT
        7
      UNION
      ALL
      SELECT
        8
      UNION
      ALL
      SELECT
        9) AS a
      CROSS JOIN
        (SELECT
          0 AS a
        UNION
        ALL
        SELECT
          1
        UNION
        ALL
        SELECT
          2
        UNION
        ALL
        SELECT
          3
        UNION
        ALL
        SELECT
          4
        UNION
        ALL
        SELECT
          5
        UNION
        ALL
        SELECT
          6
        UNION
        ALL
        SELECT
          7
        UNION
        ALL
        SELECT
          8
        UNION
        ALL
        SELECT
          9) AS b
      CROSS JOIN
        (SELECT
          0 AS a
        UNION
        ALL
        SELECT
          1
        UNION
        ALL
        SELECT
          2
        UNION
        ALL
        SELECT
          3
        UNION
        ALL
        SELECT
          4
        UNION
        ALL
        SELECT
          5
        UNION
        ALL
        SELECT
          6
        UNION
        ALL
        SELECT
          7
        UNION
        ALL
        SELECT
          8
        UNION
        ALL
        SELECT
          9) AS c) A
    CROSS JOIN
      (SELECT
        'STATUS' filter_type,
        'COMPLETED' filter_value,
        'COMPLETED' filter_label,
        '3D79D2' filter_color,
        'COMPLETED' filter_order
      UNION
      ALL
      SELECT
        'STATUS',
        'UNCOMPLETED',
        'UNCOMPLETED',
        '23A653',
        'UNCOMPLETED') F
    LEFT JOIN
      (SELECT
        WEEK_ID,
        CASE
          WHEN `status` IN ('11', '6', '10', '3', '7')
          THEN 'Completed'
          WHEN `status` IN ('12', '0', '1', '2', '9')
          THEN 'Uncompleted'
        END AS labels,
        IFNULL(COUNT(NE_CATEGORY), 0) AS total
      FROM
        CM_REPORTING AS CM
      WHERE 1 = 1
        AND WEEK_ID BETWEEN DATE_FORMAT(
          STR_TO_DATE('2017-01-01', '%Y-%m-%d'),
          '%x-%v'
        )
        AND DATE_FORMAT(
          STR_TO_DATE('2017-01-31', '%Y-%m-%d'),
          '%x-%v'
        )
        AND (
          Operational_categorization_tier_2 IN (
            'Internet Gateway',
            'HLR',
            'MSS',
            'ITP',
            'SGSN',
            'Media Gateway',
            'DNS',
            'GGSN',
            'MICS',
            'IRTD',
            'SCP',
            'Load Balancer Network',
            'SASN',
            'ADD',
            'Traffica Tnes',
            'DSP',
            'PCRF',
            'SBC',
            'M2M Gateway',
            'CDN',
            'GCS',
            'DRA',
            'AAA',
            'HLR/HSS',
            'MGW',
            'DEA',
            'Load Balancer/F5',
            'TMGW',
            'PCRF/BE-UPCC',
            'Collect Call',
            'GLR',
            'GRX Router',
            'DNS Gn',
            'Gi Switch',
            'DNS Gi',
            'M2M'
          ) Operational_categorization_tier_2 IN (
            'BTS',
            'Node B',
            'BSC',
            'RNC',
            'Transmission PDH',
            'eNode B'
          ) Operational_categorization_tier_2 IN (
            'Router Access',
            'Transmission SDH',
            'Leased Line FO',
            'Router Core',
            'IDR',
            'IP Backbone',
            'Router'
          ) Operational_categorization_tier_2 IN (
            'Power System Building',
            'Power System'
          ) Operational_categorization_tier_2 IN (
            'ACS',
            'Firewall',
            'Remote Access',
            'RSA',
            'Remote Access/RSA'
          ) Operational_categorization_tier_2 IN (
            'OSS',
            'Probing System',
            'Tools Application'
          )
      GROUP BY labels,
        WEEK_ID
      ORDER BY WEEK_ID,
        labels
      ) C
      ON A.WEEK_ID = C.WEEK_ID
      AND F.filter_value = C.labels) D
  WHERE 1 = 1
    AND WEEK_ID BETWEEN DATE_FORMAT(
      STR_TO_DATE('2017-01-01', '%Y-%m-%d'),
      '%x-%v'
    )
    AND DATE_FORMAT(
      STR_TO_DATE('2017-01-31', '%Y-%m-%d'),
      '%x-%v'
    )
  ORDER BY labels,
    filter_value ;