Confirm

Expand all | Collapse all

SQL to show progress on an inspection route

  • 1.  SQL to show progress on an inspection route

    Posted 12-01-2020 11:54
    I've got several inspection routes in confirm, and ultimately would like to create a widget for each of these routes that shows how complete they are in a gauge format so that managers can look at this on a dashboard and see progress.

    There would need to be two measures in the widget
    Nominator:      How many inspections have been completed for the route for the current day
    Demoninator:  How many inspections are scheduled to be carried out on that route for the current day

    Does anyone have any idea how I would write this in SQL to get the information to put into a widget, or if they have done anything similar??

    ------------------------------
    Rob Morris
    Telford & Wrekin Council
    Telford
    ------------------------------


  • 2.  RE: SQL to show progress on an inspection route

    Posted 12-06-2020 17:46
    Hi Rob,

    You can start with the below query, just alter the work_group codes. Then use the Inspection Check column to write the series or category expressions for the dashboard widget.

    SELECT

    inspection_route.insp_route_code,

    inspection_route.insp_route_name,

    action_officer.officer_name,

    work_group.work_group_code,

    CASE WHEN insp_route_feat.officer_code IS NULL THEN 'Inspected'

    WHEN insp_route_feat.officer_code IS NOT NULL THEN 'To Be Inspected'

    END as Inspection_Check,

    feature.feature_id,

    central_site.site_name,

    feature.site_code,

    feature.plot_number

    -- ------------------------------------------------------------------------------------------------------------

    FROM feat_attrib_type, feature, insp_route_feat, inspection_route,

    inspection_type, work_group, action_officer, central_site,

    ( SELECT insp_route_feat.insp_route_code, insp_route_feat.officer_code

    FROM insp_route_feat

    WHERE insp_route_feat.officer_code IS NOT NULL

    GROUP BY insp_route_feat.insp_route_code, insp_route_feat.officer_code

    ) Assigned_Routes

    -- ------------------------------------------------------------------------------------------------------------

    WHERE inspection_route.route_deadflag = 'N'

    AND action_officer.officer_code = Assigned_Routes.officer_code

    AND insp_route_feat.insp_route_code = Assigned_Routes.insp_route_code

    AND inspection_type.insp_type_code = inspection_route.insp_type_code

    AND work_group.work_group_code = inspection_route.work_group_code

    AND inspection_route.insp_route_code = insp_route_feat.insp_route_code

    AND inspection_route.work_group_code = insp_route_feat.work_group_code

    AND insp_route_feat.site_code = feature.site_code

    AND insp_route_feat.plot_number = feature.plot_number

    AND feature.site_code = central_site.site_code

    AND feature.site_code = feat_attrib_type.site_code

    AND feature.plot_number = feat_attrib_type.plot_number

    -- AND inspection_route.work_group_code IN ('ALL', 'M7')

    GROUP BY

    inspection_route.insp_route_code,

    inspection_route.insp_route_name,

    action_officer.officer_name,

    work_group.work_group_code,

    -- CASE WHEN insp_route_feat.officer_code IS NULL THEN 'Inspected'

    -- WHEN insp_route_feat.officer_code IS NOT NULL THEN 'To Be Inspected'

    -- END,

    insp_route_feat.officer_code,

    feature.feature_id,

    central_site.site_name,

    feature.site_code,

    feature.plot_number

    -- ------------------------------------------------------------------------------------------------------------

    ORDER BY inspection_route.insp_route_code, central_site.site_name, insp_route_feat.officer_code

    I hope this will help you to atleast make a start.

    ------------------------------
    Lalit Sharma
    Westlink Services Pty Ltd
    Eastern Creek NSW
    ------------------------------