-- Report for oscillating "NOO" productnotes
WITH noo_pn AS (
SELECT ut.PrimaryKey, COUNT(*) AS Count
FROM custopayted.user_tracking ut
JOIN custopayted.orderdetails od ON od.OrderDetailID = ut.PrimaryKey
JOIN custopayted.orders o ON o.OrderID = od.OrderID AND o.OrderStatus = 'Processing'
WHERE ut.NewValue LIKE 'NOO%'
AND ut.DB = 'custopayted'
AND ut.TableName = 'orderdetails'
AND ut.ColumnName = 'ProductNote'
GROUP BY ut.PrimaryKey
HAVING Count > 1
)

SELECT od.OrderID, od.OrderDetailID, od.ProductCode, od.ProductName, od.Quantity, od.ProductNote, p.Cost AS Vendor_Cost, pn.Count AS 'Failure #'
FROM custopayted.orderdetails od
JOIN custopayted.orders o ON o.OrderID = od.OrderID AND o.OrderStatus = 'Processing'
JOIN noo_pn pn ON pn.PrimaryKey = od.OrderDetailID
JOIN products.pins p ON p.ProductCode = od.ProductCode
WHERE od.ProductNote LIKE 'NOO%'
ORDER BY pn.Count DESC;