Untitled

  SELECT
        dt.task_nr,
        u.email,
        u.username,
        ht.canter_code AS pallet_code,
        CASE
            WHEN s.code = 'pending' THEN 'In Transit'
            WHEN s.code = 'handed_over' THEN 'Completed'
            WHEN s.code = 'skipped'THEN 'Returned to WH'
            ELSE s.code
        END AS line_status,
        r.code AS hold_reason,
        dt.created_at AS task_created_at,
        MAX(al.created_at) AS task_completed_at,
        dst_w.partner_warehouse_code AS destination_dark_store,
        src_w.partner_warehouse_code AS source_warehouse,
        IF(id_reason IS NOT NULL, alu.username, NULL) AS person_who_terminated_the_task,
        MAX(al1.created_at) AS mark_arrived_timestamp,
        MAX(al2.created_at) AS received_at_darkstore,
        COUNT(DISTINCT sh.id_shipment) AS qty_shipments,
        COUNT(DISTINCT IF(sh.id_status != 58, sh.id_shipment, 0)) AS rejected_shipments,
        COUNT(DISTINCT it.id_item) AS qty_items
    FROM noondwh.scgoms_goms.driver_task_line dtl
    LEFT JOIN noondwh.scgoms_goms.driver_task_route dtr USING (id_driver_task_route)
    LEFT JOIN noondwh.scgoms_goms.driver_task dt USING (id_driver_task)
    LEFT JOIN noondwh.scgoms_goms.warehouse dst_w ON (dst_w.id_warehouse = dtr.id_destination)
    LEFT JOIN noondwh.scgoms_goms.status s ON (s.id_status = dtl.id_status)
    LEFT JOIN noondwh.scgoms_goms.reason r ON (r.id_reason = dtl.id_hold_reason)
    LEFT JOIN noondwh.scgoms_goms.hub_transfer ht ON (dtl.entity_type, dtl.entity_id) = ('pallet', ht.id_hub_transfer)
    LEFT JOIN noondwh.scgoms_goms.hub_transfer_line htl USING (id_hub_transfer)
    LEFT JOIN noondwh.scgoms_goms.hub_transfer_group htg USING (id_hub_transfer_group)
    LEFT JOIN noondwh.scgoms_goms.shipment sh ON (htl.line_id, htl.line_type) = (sh.id_shipment, 'shipment')
    LEFT JOIN noondwh.scgoms_goms.shipment_line shl ON (sh.id_shipment = shl.id_shipment)
    LEFT JOIN noondwh.scgoms_goms.warehouse src_w ON (CAST(src_w.id_warehouse AS STRING)= sh.id_warehouse)
    LEFT JOIN noondwh.scgoms_goms.item it USING (id_item)
    LEFT JOIN noondwh.scgoms_goms.`user` u USING (id_user)
    LEFT JOIN noondwh.scgoms_goms.audit_log al ON (al.id_audit_action, al.action_key, JSON_EXTRACT_SCALAR(al.misc, '$.to')) = (18, dt.id_driver_task, 'completed')
    LEFT JOIN noondwh.scgoms_goms.`user` alu ON (alu.id_user = al.id_user)
    LEFT JOIN noondwh.scgoms_goms.audit_log al1 ON (al1.id_audit_action, al1.action_key, JSON_EXTRACT_SCALAR(al1.misc, '$.to')) = (16, dtr.id_driver_task_route, 'arrived')
    LEFT JOIN noondwh.scgoms_goms.audit_log al2 ON (al2.id_audit_action, al2.action_key, JSON_EXTRACT_SCALAR(al2.misc, '$.to')) = (11, ht.id_hub_transfer, 'closed')
    WHERE

      dtl.created_at >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY)


      AND s.code = 'skipped'


    GROUP BY 1, 2, 3, 4, 5, 6, 7, 9, 10, 11