WITH MIN_OD_ID AS (
    SELECT 
        MIN(od.orderdetailid) as min_orderdetailid
    FROM 
        custopayted.orderdetails od 
    JOIN 
        custopayted.orders o ON od.orderid = o.orderid
    WHERE 
        o.orderdate >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
),
FILTERED_ORDERDETAILS AS (
    SELECT 
        od.orderdetailid
    FROM 
        custopayted.orderdetails od 
    JOIN 
        custopayted.orders o ON od.orderid = o.orderid
    JOIN 
        MIN_OD_ID id ON od.orderdetailid >= id.min_orderdetailid
    WHERE 
        o.orderdate >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01')
        AND productnote LIKE '%| STOCK%'
)

SELECT
    pins.PIN,
    MAX(pins.Name) AS Name, 
    SUM(orderdetails.Quantity) AS Qty, 
    MAX(pins.Cost) AS COGS, 
    (SUM(orderdetails.Quantity) * MAX(pins.Cost)) AS VALUE,
    MAX(pins.Manufacturer) AS Manufacturer
FROM 
    custopayted.orderdetails
JOIN products.pins
    ON orderdetails.ProductCode = pins.ProductCode
WHERE 
    orderdetails.OrderDetailID IN (SELECT orderdetailid FROM FILTERED_ORDERDETAILS)
GROUP BY 
    pins.PIN;




/*
SELECT
	pins.PIN,
	MAX(pins.Name) AS Name, 
	SUM (orderdetails.Quantity) AS Qty, 
	MAX (pins.Cost) AS COGS, 
	(SUM (orderdetails.Quantity) * MAX (pins.Cost)) AS VALUE,
	MAX(pins.Manufacturer) AS Manufacturer
FROM 
	custopayted.orderdetails
JOIN products.pins
	ON orderdetails.ProductCode = pins.ProductCode
WHERE 
	orderdetails.OrderDetailID IN ()
GROUP BY 
	pins.PIN 
ORDER BY 
	Value DESC
*/