SELECT day, count(*) AS due_total, count(id) filter(where status = 'early') AS dispatched_early, count(id) filter(where status = 'on_due_date') AS dispatched_on_due_date, count(id) filter(where status = 'late') AS dispatched_late, count(id) filter(where status = 'not_yet_dispatched') AS not_yet_dispatched, (count(id) filter(where status in ('early', 'on_due_date')) / count(*)) AS otd_percent FROM ( SELECT soh.id, date(soh.original_estimated_dispatch_date) AS day, date(sosh.date_time_stamp) AS dispatch_date, CASE WHEN date(sosh.date_time_stamp) < date(soh.original_estimated_dispatch_date) THEN 'early' WHEN date(sosh.date_time_stamp) = date(soh.original_estimated_dispatch_date) THEN 'on_due_date' WHEN date(sosh.date_time_stamp) > date(soh.original_estimated_dispatch_date) THEN 'late' WHEN date(sosh.date_time_stamp) IS null THEN 'not_yet_dispatched' END AS status FROM liab.sales_order_header soh left join liab.sales_order_summary sos ON sos.sales_order_header_id = soh.id left join liab.sales_order_status_history sosh ON sosh.id = sos.dispatch_printed_id WHERE soh.customer_id not like "327%" -- test orders AND sos.current_status_id != 19 -- cancelled status AND YEAR(soh.original_estimated_dispatch_date) = YEAR(NOW() ) AND DATE(soh.original_estimated_dispatch_date) >= "2022-10-29" AND DATE(soh.original_estimated_dispatch_date) != DATE(NOW()) ) GROUP BY day ORDER BY day ASC
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(where status = 'early') AS dispatched_early,\n count(id) filter(whe' at line 4 (1064) (SQLExecDirectW)")
count(..) filter(...)
SUM(CASE WHEN «condition» THEN 1 ELSE 0 END)
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)