Orders Without Status History

OrdersWithoutStatusHistory.sql

# Get the number or orders without any status history
# Can be useful for debugging (e.g. it is expected that there is a status history such as "Authorize Amount"

SELECT
    date(result.created_at),
    count(result.entity_id) as Orders,
    SUM(CASE WHEN result.comments IS NULL THEN 1 ELSE 0 END) as OrdersWithoutStatuses,
    CONCAT(FLOOR(100 * SUM(CASE WHEN result.comments IS NULL THEN 1 ELSE 0 END) / count(result.entity_id)), '%') PercentageWithoutStatuses
FROM (
    SELECT
        sfo.entity_id as "entity_id",
        sfo.increment_id as "order_id",
        sfo.created_at as "created_at",
        GROUP_CONCAT(sfosh.comment) as "comments"
    FROM sales_flat_order sfo
    LEFT JOIN sales_flat_order_status_history sfosh
        ON sfo.entity_id = sfosh.parent_id
    WHERE sfo.created_at > '2015-09-01'
    GROUP BY sfo.entity_id
    ORDER BY sfo.entity_id DESC
) result
GROUP BY date(result.created_at)
ORDER BY date(result.created_at) DESC;