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
------------------------------
Original Message:
Sent: 12-01-2020 11:54
From: Rob Morris
Subject: SQL to show progress on an inspection route
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
------------------------------