recap_by_date.sql

SELECT
    terminal.name as terminal,
    city.name as kota,
    COALESCE(count(
        CASE
            WHEN r.is_arrival = true AND r.tvehicle_type_id = 4 THEN r.id
            ELSE null
            END), 0::bigint)                       AS ar_vehicle_akap,
    COALESCE(sum(
        CASE
            WHEN r.is_arrival = true AND r.tvehicle_type_id = 4 THEN r.male_passenger + r.female_passenger
            ELSE 0::numeric
            END), 0::numeric)                     AS ar_passenger_akap,
    COALESCE(count(
        CASE
            WHEN r.is_arrival = false AND r.tvehicle_type_id = 4 THEN r.id
            ELSE null
            END), 0::bigint)                       AS dp_vehicle_akap,
    COALESCE(sum(
        CASE
            WHEN r.is_arrival = false AND r.tvehicle_type_id = 4 THEN r.male_passenger + r.female_passenger
            ELSE 0::numeric
            END), 0::numeric)                     AS dp_passenger_akap,
    COALESCE(count(
        CASE
            WHEN r.is_arrival = true AND r.tvehicle_type_id = 5 THEN r.id
            ELSE null
            END), 0::bigint)                       AS ar_vehicle_akdp,
    COALESCE(sum(
        CASE
            WHEN r.is_arrival = true AND r.tvehicle_type_id = 5 THEN r.male_passenger + r.female_passenger
            ELSE 0::numeric
            END), 0::numeric)                     AS ar_passenger_akdp,
    COALESCE(count(
        CASE
            WHEN r.is_arrival = false AND r.tvehicle_type_id = 5 THEN r.id
            ELSE null
            END), 0::bigint)                       AS dp_vehicle_akdp,
    COALESCE(sum(
        CASE
            WHEN r.is_arrival = false AND r.tvehicle_type_id = 5 THEN r.male_passenger + r.female_passenger
            ELSE 0::numeric
            END), 0::numeric)                     AS dp_passenger_akdp
FROM terminal
LEFT JOIN city ON terminal.pcity_id = city.id
LEFT JOIN transportation.report r ON r.pterminal_id = terminal.id
WHERE r.date = '2023-12-01' OR r.date IS NULL
GROUP BY terminal.name, city.name
ORDER BY terminal.name ASC;