SQL > Marketing

Sales Tax report

Tax totals by US state

SELECT pro1.promoter_id, companyName, s1.stateCode, s1.stateName, v1.venuePostcode, 
    SUM(t1.transactionAmountFeeTicketSource) AS tsFee, SUM(t1.transactionAmountFeeVAT) AS tsFeeSalesTax, SUM(transactionAmountNet) AS netAmount, SUM(t1.transactionAmountNetTax) AS netAmountSalesTax, 
    SUM(t1.transactionAmountGross) AS grossAmount
FROM DBA.promoter pro1
    JOIN DBA.event e1
    JOIN DBA.venue v1
    JOIN DBA.performance per1
    JOIN DBA.transaction t1
    JOIN DBA.state s1 ON v1.venueAdd3 = s1.stateCode
WHERE pro1.promoter_id >= 88 
    AND territory_id = 3
    AND eventAccountSettled = 'Y'
    AND eventAccountSettledDateTime BETWEEN '2025-12-01 00:00:00' AND '2025-12-31 23:59:59'
    AND s1.doSalesTaxTickets = 'Y'
    AND transactionDateTime >= '2025-12-02 0:00'
    AND transactionPaymentMethod IN('TS', 'STRIPE')
    AND transactionCategory IN ('TICKETS', 'SUNDRY')
GROUP BY pro1.promoter_id, companyName, s1.stateCode, s1.stateName, v1.venuePostcode
ORDER BY stateCode, pro1.promoter_id