dave-swift

SET @dateStart = '2024-11-01 04:00:00';
SET @dateEnd = '2024-12-23 03:59:59';

WITH host_earnings AS (
	SELECT i.order_id,
		ROUND(SUM(IF(i.`status`="unpaid" OR i.`status`="unapplied", i.base_amount, 0)),2) AS open_amount,
		ROUND(SUM(IF(i.`status`="paid" OR i.`status`="applied", i.base_amount, 0)),2) AS amount_paid_to_host
	FROM host_statement_items i
	JOIN oms.events AS e ON e.order_id = i.order_id
	WHERE (e.event_date >= @dateStart AND e.event_date <= @dateEnd)