WITH AdCosts AS (
    SELECT 
        DATE(a.Date) AS AdDate, 
        SUM(a.cost) AS AdCost
    FROM custopayted.ad_campaigns a
    WHERE a.CampaignID = 339584239
    GROUP BY DATE(a.Date)
),
DysonOrders AS (
    SELECT 
        DATE(o.orderdate) AS Date,
        COUNT(od.orderdetailid) AS StockSold,
        SUM(od.totalprice) AS Sales,
        SUM(od.vendor_price) AS Cost
    FROM custopayted.orders o
    JOIN custopayted.orderdetails od ON o.orderid = od.orderid
    JOIN products.pins p ON od.ProductCode = p.ProductCode
    WHERE p.manufacturer = 'Dyson'
    AND o.orderstatus <> 'Cancelled'
    -- AND od.productnote LIKE '%| STOCK%'
    AND DATE(o.orderdate) >= '2024-04-12'
    -- AND od.productcode IN ('DY-908483-01',
-- 'DY-913507-01',
-- 'DY-917549-01',
-- 'DY-903342-04',
-- 'DY-917819-01',
-- 'DY-911489-01',
-- 'DY-914698-12',
-- 'DY-915658-01',
-- 'DY-914361-01',
-- 'DY-904246-14',
-- 'DY-916187-04',
-- 'DY-917276-03',
-- 'DY-907487-01',
-- 'DY-913031-01',
-- 'DY-916187-07',
-- 'DY-911525-03',
-- 'DY-907456-46',
-- 'DY-908658-08',
-- 'DY-913826-02',
-- 'DY-916001-08',
-- 'DY-904031-44',
-- 'DY-918554-02',
-- 'DY-911895-01',
-- 'DY-915766-01',
-- 'DY-914064-01',
-- 'DY-900279-02',
-- 'DY-916854-01',
-- 'DY-914078-01',
-- 'DY-915771-01',
-- 'DY-915553-01',
-- 'DY-903344-05',
-- 'DY-908657-02',
-- 'DY-907765-01',
-- 'DY-911666-01',
-- 'DY-914109-01',
-- 'DY-914082-01',
-- 'DY-905443-08',
-- 'DY-908658-15',
-- 'DY-915484-01',
-- 'DY-900130-24',
-- 'DY-923769-03',
-- 'DY-908634-01',
-- 'DY-904001-01',
-- 'DY-904247-49',
-- 'DY-916377-02',
-- 'DY-920216-02',
-- 'DY-920277-08',
-- 'DY-925721-01',
-- 'DY-905456-03',
-- 'DY-915647-01',
-- 'DY-915653-01',
-- 'DY-907750-01',
-- 'DY-908194-02',
-- 'DY-908661-23',
-- 'DY-914941-09',
-- 'DY-916187-08',
-- 'DY-914114-01',
-- 'DY-924595-01',
-- 'DY-916201-02',
-- 'DY-915151-01',
-- 'DY-911694-04',
-- 'DY-907765-05',
-- 'DY-915916-03',
-- 'DY-914166-01',
-- 'DY-914103-01',
-- 'DY-904031-10',
-- 'DY-907749-07',
-- 'DY-913754-01',
-- 'DY-915635-01',
-- 'DY-915499-04',
-- 'DY-918194-01',
-- 'DY-924289-05',
-- 'DY-903378-10',
-- 'DY-909001-01',
-- 'DY-915640-03',
-- 'DY-919778-02',
-- 'DY-924594-12',
-- 'DY-903432-11',
-- 'DY-916187-03',
-- 'DY-905455-02',
-- 'DY-901223-03',
-- 'DY-914695-02',
-- 'DY-914148-01',
-- 'DY-907751-01',
-- 'DY-913771-01',
-- 'DY-915169-01',
-- 'DY-914082-02',
-- 'DY-904242-04',
-- 'DY-914457-02',
-- 'DY-911645-07',
-- 'DY-911304-01',
-- 'DY-911236-01',
-- 'DY-915167-01',
-- 'DY-915166-01',
-- 'DY-913826-01',
-- 'DY-916552-01',
-- 'DY-912458-01',
-- 'DY-914085-01',
-- 'DY-919900-64',
-- 'DY-911240-01',
-- 'DY-907491-01',
-- 'DY-904141-01',
-- 'DY-914086-01',
-- 'DY-911306-02',
-- 'DY-904279-10',
-- 'DY-913764-01',
-- 'DY-914105-01',
-- 'DY-903376-01',
-- 'DY-904246-23',
-- 'DY-919780-01',
-- 'DY-907749-01',
-- 'DY-907860-01',
-- 'DY-904193-08',
-- 'DY-915152-01',
-- 'DY-903349-03',
-- 'DY-913760-01',
-- 'DY-915185-01',
-- 'DY-905033-02',
-- 'DY-904242-08',
-- 'DY-918458-01',
-- 'DY-914757-01',
-- 'DY-915552-01',
-- 'DY-916184-02',
-- 'DY-914699-01',
-- 'DY-904193-12',
-- 'DY-905450-01',
-- 'DY-913767-01',
-- 'DY-916216-02',
-- 'DY-908950-05',
-- 'DY-919900-30',
-- 'DY-923685-01',
-- 'DY-914695-01',
-- 'DY-915196-01',
-- 'DY-913766-01',
-- 'DY-914079-01',
-- 'DY-912368-01',
-- 'DY-909549-03',
-- 'DY-911606-01',
-- 'DY-915165-01',
-- 'DY-915547-01',
-- 'DY-915937-01',
-- 'DY-915769-01',
-- 'DY-917281-01',
-- 'DY-914088-01',
-- 'DY-911693-02',
-- 'DY-915485-01',
-- 'DY-911450-01',
-- 'DY-907740-12',
-- 'DY-916201-01',
-- 'DY-918226-01',
-- 'DY-921198-01',
-- 'DY-913782-01',
-- 'DY-903346-15',
-- 'DY-915740-01',
-- 'DY-965010-01',
-- 'DY-911574-01',
-- 'DY-913756-01',
-- 'DY-916853-01',
-- 'DY-911843-01',
-- 'DY-911271-01',
-- 'DY-914796-01',
-- 'DY-914192-01',
-- 'DY-965155-01',
-- 'DY-915788-01',
-- 'DY-916190-01',
-- 'DY-911235-01',
-- 'DY-916083-01',
-- 'DY-910761-01',
-- 'DY-907763-01',
-- 'DY-911037-02',
-- 'DY-905441-09',
-- 'DY-911131-01',
-- 'DY-905903-06',
-- 'DY-911399-02',
-- 'DY-907363-01',
-- 'DY-912361-01',
-- 'DY-914641-01',
-- 'DY-914701-05',
-- 'DY-915484-02',
-- 'DY-917373-02',
-- 'DY-918297-01',
-- 'DY-919376-01',
-- 'DY-920234-01',
-- 'DY-900270-05',
-- 'DY-902405-06',
-- 'DY-920595-01',
-- 'DY-964721-04',
-- 'DY-914701-06',
-- 'DY-914735-31',
-- 'DY-915531-06',
-- 'DY-919043-01',
-- 'DY-900199-26',
-- 'DY-904905-08',
-- 'DY-965667-01',
-- 'DY-917530-11',
-- 'DY-909549-01',
-- 'DY-965072-01',
-- 'DY-923991-02',
-- 'DY-925042-03',
-- 'DY-918555-01',
-- 'DY-965876-01',
-- 'DY-912278-01',
-- 'DY-911280-01',
-- 'DY-967863-05',
-- 'DY-966685-01',
-- 'DY-969042-01',
-- 'DY-969875-02',
-- 'DY-907246-05',
-- 'DY-907749-02',
-- 'DY-911279-02',
-- 'DY-909545-06',
-- 'DY-916405-01',
-- 'DY-915531-24',
-- 'DY-924366-01',
-- 'DY-966503-07',
-- 'DY-918068-02',
-- 'DY-966912-02',
-- 'DY-967485-01',
-- 'DY-915936-13',
-- 'DY-964218-01',
-- 'DY-965558-08',
-- 'DY-965661-02',
-- 'DY-966648-01',
-- 'DY-966849-01',
-- 'DY-966943-02',
-- 'DY-966951-01',
-- 'DY-967810-07',
-- 'DY-967837-02',
-- 'DY-911768-03',
-- 'DY-911685-01',
-- 'DY-911282-01',
-- 'DY-916183-02',
-- 'DY-905443-01',
-- 'DY-911299-01',
-- 'DY-911525-13',
-- 'DY-911647-01',
-- 'DY-908655-08',
-- 'DY-914108-01',
-- 'DY-914208-01',
-- 'DY-915530-01',
-- 'DY-915937-02',
-- 'DY-916538-03',
-- 'DY-916548-01',
-- 'DY-910702-10',
-- 'DY-908038-01',
-- 'DY-915934-01',
-- 'DY-965662-01',
-- 'DY-916200-01',
-- 'DY-904246-13',
-- 'DY-911337-01',
-- 'DY-924015-01',
-- 'DY-925042-01',
-- 'DY-923295-03',
-- 'DY-908676-01',
-- 'DY-911106-01',
-- 'DY-911488-01',
-- 'DY-902312-69',
-- 'DY-908661-20',
-- 'DY-917405-04',
-- 'DY-920231-02',
-- 'DY-915438-02',
-- 'DY-912516-03',
-- 'DY-965557-05',
-- 'DY-909747-04',
-- 'DY-962707-01',
-- 'DY-967612-01',
-- 'DY-918400-10',
-- 'DY-918400-16',
-- 'DY-967741-01',
-- 'DY-912153-01',
-- 'DY-964885-01',
-- 'DY-965558-06',
-- 'DY-966611-01',
-- 'DY-967451-02',
-- 'DY-968686-01',
-- 'DY-911702-01',
-- 'DY-915219-03',
-- 'DY-911039-01',
-- 'DY-911077-01',
-- 'DY-907303-17',
-- 'DY-912275-01',
-- 'DY-913747-01',
-- 'DY-914275-01',
-- 'DY-915744-02',
-- 'DY-916558-04',
-- 'DY-911434-01',
-- 'DY-917588-02',
-- 'DY-903431-12',
-- 'DY-903358-01',
-- 'DY-919779-01',
-- 'DY-914338-01',
-- 'DY-924057-02',
-- 'DY-966503-02',
-- 'DY-917083-03',
-- 'DY-966167-01',
-- 'DY-909592-05',
-- 'DY-914952-08',
-- 'DY-966503-21',
-- 'DY-904244-11',
-- 'DY-905399-17',
-- 'DY-911701-01',
-- 'DY-911920-01',
-- 'DY-915456-02',
-- 'DY-919640-03',
-- 'DY-912433-03',
-- 'DY-920506-09',
-- 'DY-912441-11',
-- 'DY-921654-01',
-- 'DY-965884-01',
-- 'DY-966550-01',
-- 'DY-966614-01',
-- 'DY-967829-01',
-- 'DY-967936-01',
-- 'DY-908654-01',
-- 'DY-915544-02',
-- 'DY-916001-04',
-- 'DY-923522-01',
-- 'DY-920116-02',
-- 'DY-920232-01',
-- 'DY-915654-01',
-- 'DY-921001-01',
-- 'DY-917390-02',
-- 'DY-920506-01',
-- 'DY-900298-17',
-- 'DY-966539-06',
-- 'DY-913992-01',
-- 'DY-914190-02',
-- 'DY-916559-01',
-- 'DY-916213-01',
-- 'DY-966503-22',
-- 'DY-910750-01',
-- 'DY-965600-01',
-- 'DY-966608-01',
-- 'DY-966609-01',
-- 'DY-966944-02',
-- 'DY-966956-01',
-- 'DY-967297-01',
-- 'DY-917506-01',
-- 'DY-914077-01',
-- 'DY-909235-01',
-- 'DY-914164-01',
-- 'DY-914255-02',
-- 'DY-915150-01',
-- 'DY-919171-02',
-- 'DY-923903-01',
-- 'DY-967064-02',
-- 'DY-917098-01',
-- 'DY-967483-01',
-- 'DY-966607-01',
-- 'DY-967408-01',
-- 'DY-907262-01',
-- 'DY-912276-01',
-- 'DY-914076-01',
-- 'DY-916212-01',
-- 'DY-917085-01',
-- 'DY-915499-07',
-- 'DY-963826-01',
-- 'DY-965082-01',
-- 'DY-920545-02',
-- 'DY-963556-01',
-- 'DY-914156-01',
-- 'DY-915374-01',
-- 'DY-915375-01',
-- 'DY-904111-08',
-- 'DY-966490-03',
-- 'DY-966613-02',
-- 'DY-967864-02',
-- 'DY-914165-01',
-- 'DY-913765-01',
-- 'DY-920214-02',
-- 'DY-919354-01',
-- 'DY-916547-02',
-- 'DY-967481-01',
-- 'DY-911857-01',
-- 'DY-911029-01',
-- 'DY-911604-03',
-- 'DY-908950-04',
-- 'DY-900188-16',
-- 'DY-914169-01',
-- 'DY-908655-03',
-- 'DY-920929-01',
-- 'DY-916243-02',
-- 'DY-911038-01',
-- 'DY-920756-01',
-- 'DY-922029-02',
-- 'DY-924531-01',
-- 'DY-966164-01',
-- 'DY-914270-04',
-- 'DY-914703-01',
-- 'DY-917086-09',
-- 'DY-914592-01',
-- 'DY-916183-01',
-- 'DY-914163-01',
-- 'DY-915456-01',
-- 'DY-966712-02',
-- 'DY-922117-02',
-- 'DY-908654-07',
-- 'DY-920983-01',
-- 'DY-949852-05',
-- 'DY-965558-15',
-- 'DY-924034-05',
-- 'DY-908043-01',
-- 'DY-911707-01',
-- 'DY-963830-02',
-- 'DY-917085-02',
-- 'DY-915741-06',
-- 'DY-917086-16',
-- 'DY-916216-01',
-- 'DY-916188-06',
-- 'DY-914197-03',
-- 'DY-914190-01',
-- 'DY-926036-05',
-- 'DY-911523-03',
-- 'DY-918400-07',
-- 'DY-911961-01',
-- 'DY-904083-07',
-- 'DY-916243-01',
-- 'DY-965558-12',
-- 'DY-920453-04',
-- 'DY-915928-12',
-- 'DY-917391-03',
-- 'DY-970151-01',
-- 'DY-965888-01',
-- 'DY-966503-18',
-- 'DY-967863-01',
-- 'DY-967911-05')
    GROUP BY DATE(o.orderdate)
)
SELECT 
    d.Date,
    d.StockSold,
    d.Sales,
    d.Cost,
    COALESCE(a.AdCost, 0) AS 'Ad Cost',
    (d.Sales - (d.Cost + COALESCE(a.AdCost, 0))) AS Profit
FROM DysonOrders d
LEFT JOIN AdCosts a ON d.Date = a.AdDate
UNION ALL
SELECT 
    'TOTAL' AS Date,
    SUM(d.StockSold) AS StockSold,
    SUM(d.Sales) AS Sales,
    SUM(d.Cost) AS Cost,
    SUM(a.AdCost) AS 'Ad Cost',
    SUM(d.Sales - (d.Cost + COALESCE(a.AdCost, 0))) AS Profit
FROM DysonOrders d
LEFT JOIN AdCosts a ON d.Date = a.AdDate
ORDER BY CASE WHEN Date = 'TOTAL' THEN 1 ELSE 0 END, Date DESC;