-- Month End Dropship Fees
-- DS tracking number costs for orders ordered on prior month but shipped this month.
SELECT
o.orderid, o.orderdate, o.orderstatus, t.shipdate, t.gateway, t.trackingnumber, t.shipment_cost, t.form

FROM custopayted.orders o
JOIN custopayted.trackingnumbers t ON t.orderid = o.orderid

WHERE (
	form LIKE 'MRC-%' OR
	form LIKE 'Marc-%' OR
	form LIKE 'UED-%' OR
	form LIKE 'ENC-%' OR
	form LIKE 'ESC-%' OR
	form LIKE 'GAR-%'
)
AND o.orderdate < CONCAT(SUBSTR(CURDATE(), 1, 8), '01 00:00:00') -- Order date was before the first of the current month.
AND t.shipdate >= CONCAT(SUBSTR(CURDATE(), 1, 8), '01 00:00:00') -- Tracking number was at or after the first of the current month.