Created
October 23, 2023 12:44
-
-
Save cesarockstar1985/56e7cbe9674a6f13e18997ed98e14f14 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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