SET @old_sql_mode = @@sql_mode;
SET @StartDate = '2010-01-01';
SET @EndDate = '2025-11-21';
SELECT
  CASE
    WHEN o.salesrep_customerid = '561739'  THEN 'Mick'
    WHEN o.salesrep_customerid = '2554513' THEN 'Josie'
    WHEN o.salesrep_customerid = '480482'  THEN 'Marta'
    WHEN o.salesrep_customerid = '879320'  THEN 'Ron'
    WHEN o.salesrep_customerid = '2451660' THEN 'Tonya'
    WHEN o.salesrep_customerid = '2451667' THEN 'Michelle'
    WHEN o.salesrep_customerid = '4225668' THEN 'Destiny'
    WHEN o.salesrep_customerid = '5000002' THEN 'Coral'
    WHEN o.salesrep_customerid = '2203115' THEN 'Ray'
    WHEN o.salesrep_customerid = '2652776' THEN 'Danah'
    WHEN o.salesrep_customerid = '2763216' THEN 'Monique'
    WHEN o.salesrep_customerid = '1915168' THEN 'Randolf'
    WHEN o.salesrep_customerid = '3117788' THEN 'Tovi'
    WHEN o.salesrep_customerid = '3117859' THEN 'Nina'
    WHEN o.salesrep_customerid = '3535982' THEN 'Kim'
    WHEN o.salesrep_customerid = '3200319' THEN 'Katherine'
    WHEN o.salesrep_customerid = '4052221' THEN 'Glenn'
    WHEN o.salesrep_customerid = '4052206' THEN 'Kimberly'
    WHEN o.salesrep_customerid = '4142564' THEN 'Jeff'
    WHEN o.salesrep_customerid = '5000000' THEN 'Paolo'
    WHEN o.salesrep_customerid = '339339'  THEN 'Royce'
    WHEN o.salesrep_customerid = '5000007' THEN 'Kylie'
    WHEN o.salesrep_customerid = '5000008' THEN 'Freya'
    WHEN o.salesrep_customerid = '5000003' THEN 'Glenn F'
    WHEN o.salesrep_customerid = '5000006' THEN 'VPW_Computer'
    WHEN o.salesrep_customerid IS NULL     THEN 'TOTAL'
    ELSE o.salesrep_customerid
  END AS salesrep_customerid ,
    COUNT(DISTINCT o.orderid) as o,
    COUNT(od.orderdetailid) as OrderDetails,
    SUM(od.totalprice) AS Sales,
    SUM(od.Quantity) as Quantity,
    (SUM(od.quantity * od.vendor_price)) AS COGS,
    ((SUM(od.totalprice) - o.coupon_discount) - SUM(od.quantity * od.vendor_price)) AS PROFIT,
    (1 / (SUM(od.quantity * od.vendor_price) + ((SUM(od.totalprice) - o.coupon_discount) - SUM(od.quantity * od.vendor_price)))) * ((SUM(od.totalprice) - o.coupon_discount) - SUM(od.quantity * od.vendor_price)) AS Profit_Margin
FROM
    orders o
    JOIN orderdetails od
        ON o.orderid = od.orderid
WHERE
    DATE(o.orderdate) BETWEEN DATE(@StartDate) AND DATE(@EndDate)
    AND o.salesrep_customerid IS NOT NULL
    AND o.orderstatus <> 'Cancelled'
    AND od.productcode NOT LIKE 'DSC-%'
    AND o.salesrep_customerid NOT IN ('267969','365719','628918','765611','865308')
GROUP BY o.salesrep_customerid  
ORDER BY o DESC;
