Confirm - Streetwork Dashboard Query

SELECT DISTINCT
  sw_header.work_header_no,
  sw_header.works_ref,
  sw_header.internal_ref,
  sw_header.work_comments,
  permit_app_notice.notice_issue_time,
  current_notice.works_desc,
  central_site.site_code,
  central_site.site_name,
  town.town_name,
  current_status.works_status_name as current_status,
  current_notice.work_start_date as work_start_date,
  current_notice_type.notice_type_name as current_notice_type_name,
  permit_app_notice.notice_type_name as permit_application_type,
  permit_resp_notice.notice_type_name as permit_response_type,
  CASE
      WHEN EXISTS
         (  SELECT 1
            FROM sw_notice_design swnd
               INNER JOIN designation d ON swnd.designation_code = d.designation_code
            WHERE swnd.work_header_no = current_notice.work_header_no
               AND swnd.work_version_no = current_notice.work_version_no
               AND swnd.site_number = current_notice.primary_site_no
               AND d.external_reference = '2'
         ) THEN 'Y'
      ELSE 'N'
   END as traffic_sensitive,
   CASE permit_app_notice.permit_status
      WHEN 'ON' THEN 'PAA Pending'
      WHEN 'PN' THEN 'Permit Pending'
      WHEN 'PA' THEN 'PAA With Permit Pending'
      WHEN 'RN' THEN 'Refused'
      WHEN 'RA' THEN 'Refused With PAA'
      WHEN 'RP' THEN 'Refused With Permit'
      WHEN 'GA' THEN 'Granted PAA'
      WHEN 'GP' THEN 'Granted Permit'
      WHEN 'OA' THEN 'PAA Variation Pending'
      WHEN 'PP' THEN 'Permit Variation Pending'
      WHEN 'PI' THEN 'Immediate Pending'
      WHEN 'RI' THEN 'Refused Immediate'
      WHEN 'VP' THEN 'Revoked Permit'
      WHEN 'VA' THEN 'Revoked PAA'
      ELSE 'Not A Permit'
   END as permit_status,
   CASE
      WHEN EXISTS
                     (SELECT 1
                                  FROM sw_notice_site swns1
                                  WHERE
                                     permit_app_notice.work_header_no = swns1.work_header_no
                                                AND permit_app_notice.work_version_no = swns1.work_version_no
                                     AND swns1.traffic_code = 'T09'
                                  ) THEN 'Y'
                  ELSE 'N'
   END as road_closure,
   CASE
      WHEN EXISTS
                     (SELECT 1
                                  FROM sw_notice_site swns1
                                  WHERE
                                     permit_app_notice.work_header_no = swns1.work_header_no
                                                AND permit_app_notice.work_version_no = swns1.work_version_no
                                     AND swns1.traffic_code = 'T07'
                                  ) THEN 'Y'
                  ELSE 'N'
   END as lane_closure,
   CASE
      WHEN EXISTS
                     (SELECT 1
                                  FROM sw_notice_site swns1
                                  WHERE
                                     permit_app_notice.work_header_no = swns1.work_header_no
                                                AND permit_app_notice.work_version_no = swns1.work_version_no
                                     AND swns1.traffic_code = 'T10'
                                  ) THEN 'Y'
                  ELSE 'N'
   END as footway_closure,
  CASE
     WHEN current_notice.permit_scheme_key IS NULL THEN 'N'ELSE 'Y'
  END as permit_notice,
  CASE
      WHEN permit_app_notice.external_reference IN ('E40210A','E40210B','E40210C','E40210D','E40310')
                     THEN 'Permit Application'
      WHEN permit_app_notice.external_reference IN ('E40311A','E40510','E41110','E41614')
                     THEN 'Permit Modification'
      ELSE 'Indeterminable'
  END as notice_type_category,
  (DATEDIFF(dd, CURRENT_TIMESTAMP, permit_app_notice.permit_date)) -
      (DATEDIFF(wk, CURRENT_TIMESTAMP, permit_app_notice.permit_date) * 2) +
      (CASE WHEN DATENAME(dw, CURRENT_TIMESTAMP) = 'Sunday' THEN 1 ELSE 0 END) +
      (CASE WHEN DATENAME(dw, permit_app_notice.permit_date) = 'Saturday' THEN 1 ELSE 0 END) -
      (SELECT COUNT(1) FROM nonworking_day nwd WHERE nwd.nonworking_date BETWEEN CURRENT_TIMESTAMP AND permit_app_notice.permit_date)
   as days_left,
   CASE
      WHEN DATEDIFF(day,CURRENT_TIMESTAMP,permit_app_notice.permit_date) = 0
         THEN 'Today'
      WHEN DATEDIFF(day,CURRENT_TIMESTAMP,permit_app_notice.permit_date) = 1
         THEN 'Tomorrow'
      WHEN DATEDIFF(day,CURRENT_TIMESTAMP,permit_app_notice.permit_date) > 1
         THEN 'More Than 1 Day'
      WHEN DATEDIFF(day,CURRENT_TIMESTAMP,permit_app_notice.permit_date) < 0
         THEN 'Deemed Date Passed'
      WHEN permit_app_notice.permit_date IS NULL
         THEN 'Permit Date Missing'
      ELSE
         'Indeterminable'
   END as response_period,
  CASE
     WHEN sw_header.cancelled_by IS NULL THEN 'N' ELSE 'Y'
  END as works_cancelled,
  current_notice.works_location,
  CASE street_work_type.external_reference
     WHEN 'E41' THEN 'Major'
                WHEN 'E42' THEN 'Standard'
                WHEN 'E43' THEN 'Minor'
                WHEN 'E44' THEN 'Immediate'
                WHEN 'E45' THEN 'Immediate'
                ELSE 'Unknown'
  END as sw_category
FROM
  sw_header
  INNER JOIN sw_notice_header current_notice ON sw_header.work_header_no = current_notice.work_header_no
  AND sw_header.work_version_no = current_notice.work_version_no
  INNER JOIN notice_type current_notice_type ON current_notice.notice_type_code = current_notice_type.notice_type_code
  INNER JOIN sw_works_status current_status ON current_notice.works_status_code = current_status.works_status_code
  INNER JOIN organisation ON sw_header.organise_code = organisation.organise_code
  INNER JOIN organise_group ON organisation.org_group_code = organise_group.org_group_code
  LEFT JOIN (
    SELECT
      a.work_header_no as work_header_no,
      a.work_version_no as work_version_no,
      a.permit_date as permit_date,
      notice_type.notice_type_name as notice_type_name,
                 a.notice_issue_time,
      a.permit_status,
                 notice_type.external_reference
               FROM
      sw_notice_header a
      INNER JOIN notice_type ON a.notice_type_code = notice_type.notice_type_code
    WHERE
      notice_type.external_reference IN (
        'E40210A',
        'E40210B',
        'E40210C',
        'E40210D',
        'E40310',
        'E40311A',
        'E40510',
        'E41110')
       and a.work_version_no = (select max(work_version_no) from sw_notice_header b where b.work_header_no = a.work_header_no)
  ) permit_app_notice ON sw_header.work_header_no = permit_app_notice.work_header_no
  LEFT JOIN (
    SELECT
      a.work_header_no as work_header_no,
      a.work_version_no as work_version_no,
      a.permit_date as permit_date,
      notice_type.notice_type_name as notice_type_name
    FROM
      sw_notice_header a
      INNER JOIN notice_type ON a.notice_type_code = notice_type.notice_type_code
    WHERE
      notice_type.external_reference IN ('E40900','E41610','E41611','E41612','E41613','E41614')
      and a.work_version_no = (select max(work_version_no) from sw_notice_header b where b.work_header_no = a.work_header_no)
  ) permit_resp_notice ON sw_header.work_header_no = permit_resp_notice.work_header_no
  INNER JOIN sw_notice_site ON
     current_notice.work_header_no = sw_notice_site.work_header_no
                AND current_notice.work_version_no = sw_notice_site.work_version_no
                AND current_notice.primary_site_no = sw_notice_site.site_number
  INNER JOIN central_site ON
     sw_notice_site.site_code = central_site.site_code
  INNER JOIN locality ON
     central_site.locality_id = locality.locality_id
 INNER JOIN town ON
     locality.town_id = town.town_id
  INNER JOIN street_work_type ON
     current_notice.street_work_code = street_work_type.street_work_code
WHERE
  current_notice.notice_type_code != '0900'
  AND (permit_resp_notice.work_version_no IS NULL OR permit_resp_notice.work_version_no < permit_app_notice.work_version_no)
  AND current_notice.permit_date > (GETDATE() - 1)
  AND current_notice.permit_scheme_key IS NOT NULL