Freight Identity Risk Report: October 2026

SELECT substring(add_date,1,6) AS ym, count(*) AS n WHERE add_date >= '20241001' AND add_date < '20261001' GROUP BY ym ORDER BY ym

Per state:
SELECT phy_state AS st, substring(add_date,1,6) AS ym, count(*) AS n WHERE add_date >= '20241001' AND add_date < '20261001' GROUP BY st, ym ORDER BY st, ym

SELECT substring(orig_served_date,7,4) || substring(orig_served_date,1,2) AS ym, count(distinct dot_number) AS n WHERE original_action_desc = 'GRANTED' AND dot_number != '00000000' AND (substring(orig_served_date,7,4) || substring(orig_served_date,1,2)) BETWEEN '202410' AND '202605' GROUP BY ym ORDER BY ym

Per state:
SELECT @c.phy_state AS st, substring(orig_served_date,7,4) || substring(orig_served_date,1,2) AS ym, count(distinct dot_number) AS n JOIN @az4n-8mr2 AS c ON dot_number::number = @c.dot_number WHERE original_action_desc = 'GRANTED' AND dot_number != '00000000' AND (substring(orig_served_date,7,4) || substring(orig_served_date,1,2)) BETWEEN '202410' AND '202605' GROUP BY st, ym ORDER BY st, ym

SELECT substring(status_change_date,1,6) AS ym, count(distinct usdot_number) AS n WHERE upper(reason) = 'GRANTED' AND usdot_number IS NOT NULL AND status_change_date >= '20260515' AND status_change_date < '20261001' GROUP BY ym ORDER BY ym

Per state:
SELECT @c.phy_state AS st, substring(status_change_date,1,6) AS ym, count(distinct usdot_number) AS n JOIN @az4n-8mr2 AS c ON usdot_number::number = @c.dot_number WHERE upper(reason) = 'GRANTED' AND usdot_number IS NOT NULL AND status_change_date >= '20260515' AND status_change_date < '20261001' GROUP BY st, ym ORDER BY st, ym

SELECT substring(order2_effective_date,7,4) || substring(order2_effective_date,1,2) AS ym, count(distinct dot_number) AS n WHERE order2_type_desc = 'INVOLUNTARY REVOCATION' AND dot_number != '00000000' AND (substring(order2_effective_date,7,4) || substring(order2_effective_date,1,2)) BETWEEN '202410' AND '202605' GROUP BY ym ORDER BY ym

Per state:
SELECT @c.phy_state AS st, substring(order2_effective_date,7,4) || substring(order2_effective_date,1,2) AS ym, count(distinct dot_number) AS n JOIN @az4n-8mr2 AS c ON dot_number::number = @c.dot_number WHERE order2_type_desc = 'INVOLUNTARY REVOCATION' AND dot_number != '00000000' AND (substring(order2_effective_date,7,4) || substring(order2_effective_date,1,2)) BETWEEN '202410' AND '202605' GROUP BY st, ym ORDER BY st, ym

SELECT substring(status_change_date,1,6) AS ym, count(distinct usdot_number) AS n WHERE op_auth_status = 'Inactive' AND (starts_with(upper(reason), 'INVOLUNTARY SUSPENSION') OR starts_with(upper(reason), 'INACTIVATED') OR upper(reason) = 'REVOKED') AND usdot_number IS NOT NULL AND status_change_date >= '20260515' AND status_change_date < '20261001' GROUP BY ym ORDER BY ym

Per state:
SELECT @c.phy_state AS st, substring(status_change_date,1,6) AS ym, count(distinct usdot_number) AS n JOIN @az4n-8mr2 AS c ON usdot_number::number = @c.dot_number WHERE op_auth_status = 'Inactive' AND (starts_with(upper(reason), 'INVOLUNTARY SUSPENSION') OR starts_with(upper(reason), 'INACTIVATED') OR upper(reason) = 'REVOKED') AND usdot_number IS NOT NULL AND status_change_date >= '20260515' AND status_change_date < '20261001' GROUP BY st, ym ORDER BY st, ym

SELECT substring(order1_serve_date,1,6) AS ym, count(distinct usdot_number) AS n WHERE order1_type_desc = 'Operating Authority Involuntary Suspension Notice' AND usdot_number IS NOT NULL AND order1_serve_date >= '20260515' AND order1_serve_date < '20261001' GROUP BY ym ORDER BY ym

SELECT substring(cancl_effective_date,7,4) || substring(cancl_effective_date,1,2) AS ym, count(distinct dot_number) AS n WHERE mod_col_1 = 'Cancelled' AND dot_number != '00000000' AND (substring(cancl_effective_date,7,4) || substring(cancl_effective_date,1,2)) BETWEEN '202410' AND '202605' GROUP BY ym ORDER BY ym

Per state:
SELECT @c.phy_state AS st, substring(cancl_effective_date,7,4) || substring(cancl_effective_date,1,2) AS ym, count(distinct dot_number) AS n JOIN @az4n-8mr2 AS c ON dot_number::number = @c.dot_number WHERE mod_col_1 = 'Cancelled' AND dot_number != '00000000' AND (substring(cancl_effective_date,7,4) || substring(cancl_effective_date,1,2)) BETWEEN '202410' AND '202605' GROUP BY st, ym ORDER BY st, ym

SELECT substring(cancl_effective_date,1,6) AS ym, count(distinct usdot_number) AS n WHERE filing_status_reason = 'CANCEL' AND usdot_number IS NOT NULL AND cancl_effective_date >= '20260515' AND cancl_effective_date < '20261001' GROUP BY ym ORDER BY ym

Per state:
SELECT @c.phy_state AS st, substring(cancl_effective_date,1,6) AS ym, count(distinct usdot_number) AS n JOIN @az4n-8mr2 AS c ON usdot_number::number = @c.dot_number WHERE filing_status_reason = 'CANCEL' AND usdot_number IS NOT NULL AND cancl_effective_date >= '20260515' AND cancl_effective_date < '20261001' GROUP BY st, ym ORDER BY st, ym