SELECT count(*)
FROM ecms.sales_order so
LEFT JOIN ecms.sales_order_item soi ON soi.sales_order_id = so.id
LEFT JOIN ecms.sales_order_payment sop ON sop.sales_order_id = so.id
LEFT JOIN ecms.customer cu ON cu.id = so.customer_id
LEFT JOIN ecms.history h ON h.transaction_id = so.transaction_id
LEFT JOIN ecms.product p ON p.id = soi.product_id
LEFT JOIN ecms.product_price pp ON pp.product_id = p.id AND pp.date_created <= so.date_created AND so.date_created BETWEEN COALESCE(pp.from_date, so.date_created) AND COALESCE(pp.thru_date, so.date_created)
LEFT JOIN ecms.product_price pp2 ON pp2.product_id = p.id
LEFT JOIN ecms.product_cost pc ON pc.product_id = p.id AND pc.date_created <= so.date_created AND so.date_created BETWEEN COALESCE(pc.from_date, so.date_created) AND COALESCE(pc.thru_date, so.date_created)
LEFT JOIN ecms.product_cost pc2 ON pc2.product_id = p.id
LEFT JOIN ecms.merchant m ON m.id = p.merchant_id
LEFT JOIN ecms.brand b ON b.id = p.brand_id
LEFT JOIN ecms.route_number rn ON rn.facility_id = so.delivery_location_id
LEFT JOIN (
SELECT

c0.id,
IFNULL(c1.name, c0.name) as cat_1,
c0.name as cat_2

FROM ecms.category c0
LEFT JOIN ecms.category_rollup cr ON category_id = c0.id
LEFT JOIN ecms.category c1 ON c1.id = cr.parent_category_id
)c ON c.id = p.category_id

WHERE so.order_type = 'ITEM FULFILLMENT'
AND so.status IN ('FOR CONFIRMATION','PL GEN COMPLETED','CONFIRMED','FOR SORTING','FOR DELIVERY','IN-TRANSIT','UNSERVED','LOST','DELIVERED TO STORE','CLAIMED','FOR PULL OUT','FOR RETURN TO MERCHANT','RETURNED TO MERCHANT','FOR REFUND','REFUNDED','DELIVERED TO WAREHOUSE_REGIONAL','IN-TRANSIT_REGIONAL','ARRIVED AT STORE','ARRIVED AT WAREHOUSE','ON VEHICLE TO STORE','ON VEHICLE TO WAREHOUSE')
AND p.name NOT IN ('CLiQQ Live Test Product','Live Test Product','711 TOUR Race Kit 48KM 500','711 TOUR Race Kit 106KM 900')

AND date(so.order_date) BETWEEN '2020-01-23 00:00:00' AND '2020-01-23 23:59:59'
AND so.requesting_device IN ('PWA','KIOSK','CLIQQ')