-- 1563 REPORTS 2.1 Tracking Numbers Statistics

SELECT
Date(shipdate) as Date,
CASE
	WHEN MAX(ShippingMethodID) IN (301,302,303,305,306,308,1039,1041,1042) THEN '1. USPS First Class'
	WHEN MAX(ShippingMethodID) IN (205,307) THEN '2. USPS Priority Mail'
	WHEN MAX(ShippingMethodID) IN (210,211,401,403,404,405,406,407,408,411,412,413,415,416,417,418) THEN '3. USPS International'
	WHEN MAX(ShippingMethodID) = 304 THEN '4. FedEx Smart Post'
	WHEN MAX(ShippingMethodID) IN (141,950,951,952) THEN '5. FedEx Ground/Home'
	WHEN MAX(ShippingMethodID) = 142 THEN '6. FedEx Drop Ship'
	WHEN MAX(ShippingMethodID) = 1059 THEN '7. UPS MI International'
	WHEN MAX(ShippingMethodID) = 410 THEN '8. DHL'
	WHEN MAX(ShippingMethodID) = 109 THEN '9. UPS Horizon DS'
	WHEN MAX(ShippingMethodID) IN (142,201,202,307) THEN '10. Marcone DS First Class/SmartPost'
	WHEN MAX(ShippingMethodID) IN (1058) THEN '11. FedEx Express One-Rate'
	WHEN MAX(ShippingMethodID) IN (1055,1056) THEN '12. Customer/Vendor Returns'
	ELSE CAST(ShippingMethodID AS CHAR(3))
END AS Carrier,
COUNT (ShippingMethodID) AS NumberShipped,
ROUND(AVG (Shipment_Cost),2) AS AverageCost,
ROUND(AVG(CAST(Package AS DECIMAL)),2) AS AverageWeightLBS,
ROUND(AVG(DaysToDeliver * 1.00),1) AS AverageDaysToDeliver,
SUM(CASE WHEN Package IS NULL OR Package = '' THEN 1 ELSE 0 END) AS NotBilled,
SUM(CASE WHEN Form IS NULL OR Form = '' THEN 1 ELSE 0 END) AS Undelivered,
ROUND(SUM(CASE WHEN Package IS NULL OR Package = '' THEN 0 ELSE 1 END) * 1.00 * 100 / COUNT(ShippingMethodID * 1.00) * 1.00, 2) AS PercentBilled,
ROUND(SUM(CASE WHEN Form IS NULL OR Form = '' THEN 0 ELSE 1 END) * 1.00 * 100 / COUNT(ShippingMethodID * 1.00) * 1.00, 2) AS PercentDelivered

FROM trackingnumbers
LEFT OUTER JOIN
(
	SELECT TrackingNumber, DATEDIFF(STR_TO_DATE(RIGHT(form, LENGTH(form)-LOCATE('-',form)), '%m/%d/%Y %h:%i %p'), ShipDate) AS DaysToDeliver
	FROM trackingnumbers
	WHERE ShipDate > '2020-12-31' AND OrderID != 0 AND (Form LIKE '%/%/2024' OR Form Like '%/%/2023' OR Form LIKE '%/%/2022') AND ShippingMethodID != 606
) AS DTD ON trackingnumbers.Trackingnumber = DTD.TrackingNumber

WHERE ShipDate > '2020-12-31' AND OrderID != 0
AND trackingnumbers.OrderID != 1122330
AND trackingnumbers.Package REGEXP '^[\.0-9]+$'
GROUP BY ShippingMethodID, Date(shipdate)
HAVING MAX(ShippingMethodID) != 606
ORDER BY Date(shipdate) DESC, Carrier