Confirm

Expand all | Collapse all

Streetworks SQL Data for Dashboard

  • 1.  Streetworks SQL Data for Dashboard

    Posted 08-05-2019 09:21
      |   view attached

    Hello,

    I have written a SQL statement for streetworks but it seems to display duplicates.  Does anyone know how do i resolve this?

    Thanks

    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

          sw_notice_header.work_header_no as work_header_no,

          MAX(sw_notice_header.work_version_no) as work_version_no,

          sw_notice_header.permit_date as permit_date,

          notice_type.notice_type_name as notice_type_name,

                     sw_notice_header.notice_issue_time,

          sw_notice_header.permit_status,

                     notice_type.external_reference

                   FROM

          sw_notice_header

          INNER JOIN notice_type ON sw_notice_header.notice_type_code = notice_type.notice_type_code

        WHERE

          notice_type.external_reference IN (

            'E40210A',

            'E40210B',

            'E40210C',

            'E40210D',

            'E40310',

            'E40311A',

            'E40510',

            'E41110')

          GROUP BY

                        sw_notice_header.work_header_no,

                                  sw_notice_header.permit_date,

          notice_type.notice_type_name,

                     sw_notice_header.notice_issue_time,

          sw_notice_header.permit_status,

                     notice_type.external_reference

      ) permit_app_notice ON sw_header.work_header_no = permit_app_notice.work_header_no

      LEFT JOIN (

        SELECT

          sw_notice_header.work_header_no as work_header_no,

          MAX(sw_notice_header.work_version_no) as work_version_no,

          sw_notice_header.permit_date as permit_date,

          notice_type.notice_type_name as notice_type_name

        FROM

          sw_notice_header

          INNER JOIN notice_type ON sw_notice_header.notice_type_code = notice_type.notice_type_code

        WHERE

          notice_type.external_reference IN ('E40900','E41610','E41611','E41612','E41613','E41614')

                   GROUP BY

                     sw_notice_header.work_header_no,

                     sw_notice_header.permit_date,

          notice_type.notice_type_name

      ) 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

    ------------------------------
    Afzal Hussain
    Knowledge Community Shared Account
    Shelton CT
    ------------------------------

    Attachment(s)



  • 2.  RE: Streetworks SQL Data for Dashboard

    Posted 08-06-2019 06:41
    Here's a possible fix:
    https://pastebin.com/ivgiaBT2

    The issue is with the two times you've used 'max' in the original query. It looks like you were trying to get the row with the highest value in both cases - you can't do this in a single step, such a query has to work in two stages, once to find the actual maximum value and a second time to return the row that has said maximum value. Often you'll see this with the tables labelled 'a' and 'b'.

    There's a ton of pages that provide a better explanation than me for this too, try googling 'find row with highest value in column'.

    ------------------------------
    Gary Collins
    Stockport
    ------------------------------