Skip to content

Instantly share code, notes, and snippets.

@cesarockstar1985
Created October 23, 2023 12:44
Show Gist options
  • Save cesarockstar1985/56e7cbe9674a6f13e18997ed98e14f14 to your computer and use it in GitHub Desktop.
Save cesarockstar1985/56e7cbe9674a6f13e18997ed98e14f14 to your computer and use it in GitHub Desktop.
SELECT
distinct rw.id,
coalesce(rw.disapproval_comment) as disapproval_comment,
rw.end_day,
rw.friday,
rw.monday,
rw.saturday,
rw.start_day,
rw.sunday,
rw.thursday,
rw.tuesday,
rw.wednesday,
rw.week,
c.name as company,
u.full_name as user,
rw.status,
array_agg(distinct lsz.description) as location_sub_zone_list,
array_agg(distinct lz.description) as location_zone_list,
array_agg(distinct podlsz.description) as pod_location_sub_zone_list,
array_agg(distinct podlz.description) as pod_location_zone_list,
e.id,
e.comments,
e.event_date,
e.latitude as event_latitude,
e.longitude as event_longitude,
coalesce(et.description, '') as description,
coalesce(et.code, '') as code,
rd.id as route_detail_id,
rd.visit_status,
coalesce(rd.ev_reason, '') as ev_reason,
pod.name,
pod.plant_name,
pod.plant_number,
pod.latitude as pod_latitude,
pod.longitude as pod_longitude,
podc.description as category,
coalesce(et.description, '') as event
FROM
route_week rw
LEFT JOIN route r ON rw.id = r.route_week_id
LEFT JOIN route_detail rd ON r.id = rd.route_id
LEFT JOIN point_of_delivery pod ON rd.point_of_delivery_id = pod.id
LEFT JOIN point_of_delivery_category podc ON pod.point_of_delivery_category_id = podc.id
LEFT JOIN company c ON rw.company_id = c.id
LEFT JOIN users u ON rw.user_id = u.id
LEFT JOIN user_location_sub_zone sz ON u.id = sz.user_id
LEFT JOIN location_sub_zone lsz ON sz.location_sub_zone_id = lsz.id
LEFT JOIN location_zone lz ON lsz.location_zone_id = lz.id
LEFT JOIN location_sub_zone podlsz ON pod.location_sub_zone_id = podlsz.id
LEFT JOIN location_zone podlz ON pod.location_zone_id = podlz.id
LEFT JOIN event e ON e.route_detail_id = rd.id
LEFT JOIN event_type et ON e.event_type_id = et.id
AND et.code != '_ha'
WHERE
rw.deleted is not true
AND rw.deleted = 'FALSE'
AND sz.deleted = 'FALSE'
AND r.deleted = 'FALSE'
AND rd.tag ISNULL
AND rd.deleted = 'FALSE'
AND rd.visit_status != '4'
AND e.deleted = 'FALSE'
AND rw.start_day >= '2023-01-23 00:00:00-03'
AND rw.end_day <= '2023-01-29 23:59:59-03'
GROUP BY
rw.id,
rw.approval_date,
rw.disapproval_comment,
rw.end_day,
rw.friday,
rw.monday,
rw.saturday,
rw.start_day,
rw.sunday,
rw.thursday,
rw.tuesday,
rw.wednesday,
rw.week,
c.name,
u.full_name,
rw.status,
e.id,
et.description,
et.code,
rd.ev_reason,
podc.description,
pod.name,
pod.plant_name,
pod.plant_number,
rd.id,
rd.visit_status,
pod.latitude,
pod.longitude
ORDER BY
rw.start_day ASC,
e.event_date ASC,
u.full_name ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment