SELECT 
    DATE_ADD('1900-01-01', INTERVAL (DATEDIFF(o.OrderDate, '1900-01-01') DIV 7) * 7 DAY) AS MyDate,
    p.Manufacturer,
    SUM(od.Quantity * od.ProductPrice) AS Total_Sale,
    SUM(od.Quantity * od.Vendor_price) AS Total_Cost,
    MAX(p.Vertical) AS Categories
FROM 
    orderdetails od
    JOIN orders o ON od.OrderID = o.OrderID
    LEFT JOIN products.client_map ON od.ProductCode = client_map.ProductCode
    LEFT JOIN products.pins p ON client_map.PIN = p.PIN
WHERE 
    ((od.ProductNote NOT LIKE '%NLA%' AND od.ProductNote NOT LIKE '%cancel%') OR od.ProductNote IS NULL OR od.ProductNote LIKE '%NOO - %')
    AND o.OrderDate >= '2019-01-01 00:00:00'
    AND o.OrderDate <= '2023-12-31 23:59:59'
    AND o.OrderStatus != 'Cancelled' 
    AND od.Quantity != 0
    -- This condition excludes the current week
    AND WEEK(o.OrderDate, 1) < WEEK(CURDATE(), 1)
GROUP BY 
    DATE_ADD('1900-01-01', INTERVAL (DATEDIFF(o.OrderDate, '1900-01-01') DIV 7) * 7 DAY), 
    p.Manufacturer
ORDER BY 
    MyDate DESC, 
    p.Manufacturer ASC