SELECT 
  t1.MyDate,
  t1.Order_Count,
  ROUND((t1.Product_Sales - t2.Coupon_Discount), 2) AS Product_Sales,
  t1.COGS,
  ROUND(100*t1.COGS/(t1.Product_Sales - t2.Coupon_Discount),2) AS COGS_Ratio,
  ROUND((t1.Product_Sales - t2.Coupon_Discount) - t1.COGS, 2) AS Product_Gross_Profit,
  t2.Coupon_Discount,
  t1.Line_Count,
  t1.Quantity_Count
FROM (
  SELECT 
    DATE_ADD('2000-01-01', INTERVAL DATEDIFF(o.OrderDate, '2000-01-01') DAY) AS MyDate,
    COUNT(DISTINCT o.OrderID) AS Order_Count,
    ROUND(SUM(od.ProductPrice * od.Quantity),2) AS Product_Sales,
    ROUND(SUM(od.Vendor_Price * od.Quantity),2) AS COGS,
    COUNT(DISTINCT od.OrderDetailID) AS Line_Count, 
    SUM(od.Quantity) AS Quantity_Count 
  FROM orderdetails od
  JOIN orders o 
    ON od.OrderID = o.OrderID 
  WHERE o.OrderStatus <> 'Cancelled'
  AND od.Quantity <> 0 
  AND o.OrderDate > '2022-01-01' 
  AND od.ProductCode NOT LIKE 'DSC-%'
  GROUP BY DATE_ADD('2000-01-01', INTERVAL DATEDIFF(o.OrderDate, '2000-01-01') DAY)
) AS t1
LEFT JOIN (
  SELECT 
    DATE_ADD('2000-01-01', INTERVAL DATEDIFF(o.OrderDate, '2000-01-01') DAY) AS MyDate,
    ROUND(SUM(o.coupon_discount),2) AS Coupon_Discount
  FROM orders o 
  WHERE o.OrderStatus <> 'Cancelled'
  AND o.OrderDate > '2024-04-01' 
  GROUP BY DATE_ADD('2000-01-01', INTERVAL DATEDIFF(o.OrderDate, '2000-01-01') DAY)
) AS t2 ON t1.MyDate = t2.MyDate
ORDER BY t1.MyDate DESC


-- SELECT
-- DATE_ADD('2000-01-01', INTERVAL DATEDIFF(o.OrderDate, '2000-01-01') DAY) AS MyDate,
-- COUNT(DISTINCT o.OrderID) AS Order_Count,
-- ROUND(SUM(od.ProductPrice * od.Quantity),2) AS Product_Sales,
-- SUM(o.Coupon_Discount) AS Coupon_SUM,
-- ROUND(SUM(od.Vendor_Price * od.Quantity),2) AS COGS,
-- ROUND(100*SUM(od.Vendor_Price* od.Quantity)/SUM(od.ProductPrice * od.Quantity),2) AS COGS_Ratio,
-- ROUND(SUM(od.ProductPrice * od.Quantity) - SUM(od.Vendor_Price * od.Quantity),2) AS Product_Gross_Profit, 
-- COUNT(DISTINCT od.OrderDetailID) AS Line_Count, 
-- SUM(od.Quantity) AS Quantity_Count 
-- FROM orderdetails od
-- JOIN orders o 
-- ON od.OrderID = o.OrderID 
-- LEFT JOIN (SELECT OrderID, SUM (Shipment_Cost) AS ShipExpense FROM trackingnumbers WHERE OrderID > 400000 GROUP BY OrderID) AS Shipping 
-- ON o.OrderID = Shipping.OrderID
-- WHERE o.OrderStatus <> 'Cancelled'
-- AND (od.Vendor_Price IS NOT NULL OR Vendor_Price = '')  
-- AND od.Quantity <> 0 
-- AND o.OrderDate > '2022-01-01' 
-- AND od.ProductCode NOT LIKE 'DSC-%'
-- GROUP BY DATE_ADD('2000-01-01', INTERVAL DATEDIFF(o.OrderDate, '2000-01-01') DAY)
-- ORDER BY DATE_ADD('2000-01-01', INTERVAL DATEDIFF(o.OrderDate, '2000-01-01') DAY) DESC