/* ============================================================
Service Requests with Linked Request and Latest Work Orders
- Uses consolidated views to minimize joins
- Filters by specific customer_id (example: 1356)
- Clear English aliases and comments for portfolio showcase
============================================================ */
SELECT
-- Request info
rq.request_code AS [Request No],
rq.request_type AS [Request Type],
rq.customer_name AS [Customer],
rq.request_date AS [Request Date],
rq.request_notes AS [Request Notes],
rq.site_name AS [Site],
[Link] AS [City],
rq.contact_name AS [Contact],
rq.workgroup_name AS [Workgroup],
-- Asset details
rq.asset_code AS [Asset Code],
rq.asset_desc AS [Asset Description],
rq.serial_no AS [Serial No],
-- Request status
rq.status_desc AS [Status],
rq.progress_state AS [Progress],
-- Linked request (parent request)
rq_hot.request_code AS [Linked Request No],
rq_hot.request_type AS [Linked Request Type],
rq_hot.customer_name AS [Linked Customer],
rq_hot.request_date AS [Linked Request Date],
rq_hot.request_notes AS [Linked Request Notes],
-- Last work order on current request
wo.work_order_code AS [Work Order No],
wotype.work_order_type_desc AS [Work Order Type],
[Link] AS [Technician Comment],
wo.work_start_date AS [Work Start],
wo.work_end_date AS [Work End],
wo.work_duration AS [Work Duration],
res.resolution_desc AS [Resolution],
-- Closure note for the request
close_note.notes AS [Closure Comment],
-- Last work order on linked request (technician note)
hot_note.notes AS [Linked Work Order Comment]
FROM v_requests AS rq
INNER JOIN v_requests AS rq_hot
ON rq_hot.parent_request_id = rq.request_id -- Linked (parent) request
LEFT JOIN v_first_last_activities AS fl
ON fl.request_id = rq.request_id -- First/last activities for current request
LEFT JOIN v_first_last_activities AS fl_hot
ON fl_hot.request_id = rq_hot.request_id -- First/last activities for linked
request
-- Last work order for current request
LEFT JOIN t_work_orders AS wo
ON wo.work_order_id = fl.last_itv_id
LEFT JOIN d_work_order_types AS wotype
ON wotype.work_order_type_id = wo.work_order_type_id
LEFT JOIN t_work_order_notes AS wonote
ON wonote.work_order_id = wo.work_order_id
LEFT JOIN d_resolutions AS res
ON res.resolution_id = wo.resolution_id
-- Last work order for linked request (technician note)
LEFT JOIN t_work_orders AS wo_hot
ON wo_hot.work_order_id = fl_hot.last_itv_id
LEFT JOIN t_work_order_notes AS hot_note
ON hot_note.work_order_id = wo_hot.work_order_id
-- Closure note on the request
LEFT JOIN t_request_closure_notes AS close_note
ON close_note.request_id = rq.request_id
WHERE
-- Specific customer (example)
rq.customer_id = 1356
-- Request nature/type
AND rq.request_type = 'Repair'
-- Requests created during the 2022 calendar year (SARGable)
AND (
rq.request_date >= '2022-01-01'
AND rq.request_date < '2023-01-01'
-- Closed requests (as per business logic)
-- When request_end_id IS NULL → the request is still open or in progress.
-- When request_end_id IS NOT NULL → the request is closed.
-- The ID assigned to request_end_id references a specific closure type in the
resolution table,
-- indicating the reason for closure (e.g., 'Cancelled', 'Duplicate', 'Resolved', 'Closed',
etc.).
-- This allows filtering only finalized requests while distinguishing between different
closure outcomes.
AND rq.request_end_id IS NOT NULL
ORDER BY
rq.request_code;