Dev Team Portal

Appearance

SQL > Custom Queries ​

Queries/exports for specific EOs

Stratford Literary Festival [32000] ​

Top 10 Customers ​

EO wanted to reward top purchasers for a given year (2025)

sql
SELECT TOP 10 contactName, contactEmail, COUNT(transaction_id) AS numBookings
FROM DBA.event e1
 JOIN DBA.venue v1 ON e1.event_id = v1.event_id
 JOIN dba.performance p1 ON v1.venue_id = p1.venue_id
 JOIN dba.transaction t1 ON p1.performance_id = t1.performance_id
 JOIN DBA.patron pat1 ON t1.patron_id = pat1.patron_id
WHERE e1.promoter_id = 32000
 AND transactionCategory = 'TICKETS'
 AND transactionType = 'DR'
 AND transactionDateTime BETWEEN '2025-02-14' AND '2025-11-03'
 AND contactName <> 'anon anon'
GROUP BY contactName, contactEmail
ORDER BY numBookings DESC

PhoenixFest [136706] ​

Price category description and hint update ​

EO modified description and hint after onsale and wanted us to update and resend etickets

sql
/*
    Update price category hint in transactionJSON
*/
UPDATE DBA.transaction 
SET transactionJSON = replace(transactionJSON,'Workshop (+ Phoenix Comic Con - SAT AM)','Workshop ticket (+ Phoenix Comic Con - SAT AM)')
WHERE transaction_id IN(
    SELECT t1.transaction_id 
    FROM DBA.venue JOIN DBA.performance JOIN DBA.transaction t1
    WHERE event_id = 1239693
     AND transactionJSON LIKE('%Workshop (+ Phoenix Comic Con - SAT AM)%')
)

/*
    Update price category description in transactionJSON
*/
UPDATE DBA.transaction 
SET transactionJSON = replace(transactionJSON,'1:30pm - 5:30pm','9am - 1pm')
WHERE transaction_id IN(
    SELECT t1.transaction_id 
    FROM DBA.venue JOIN DBA.performance JOIN DBA.transaction t1
    WHERE event_id = 1239693
     AND transactionJSON LIKE('%1:30pm - 5:30pm (Sat 25.04)%')
)

/*
    Update price category description on seatingSeat records
*/
UPDATE DBA.seatingSeat
SET seatingSeatBookedDescription = 'Workshop ticket (+ Phoenix Comic Con - SAT AM)'
WHERE seatingSeat_id IN(
    SELECT ss1.seatingSeat_id 
    FROM DBA.venue JOIN DBA.performance JOIN DBA.transaction t1 JOIN DBA.seatingSeat ss1
    WHERE event_id = 1239693
     AND ss1.seatingSeatBookedDescription = 'Workshop (+ Phoenix Comic Con - SAT AM)'
)

Other ​

Organization summary over period by account ​

A breakdown of income and fees over a specific period, grouped by EO account and linked by a specific user email address. Used in the past for:

sql
SELECT pr1.promoter_id AS ID, companyName AS Company, 
    promoterBookingFeeOption AS FeeOption, 
    SUM(transactionAmountNet) AS Net, 
    SUM(transactionAmountFeeTicketSource) AS FeeNet, 
    SUM(transactionAmountFeeVAT) AS FeeVAT, 
    SUM(transactionAmountGross) AS Gross
FROM DBA.promoterUserAccount pua1
    JOIN DBA.promoter pr1 ON pua1.promoter_id = pr1.promoter_id
    JOIN DBA.event e1 ON pr1.promoter_id = e1.promoter_id
    JOIN DBA.venue v1 ON e1.event_id = v1.event_id
    JOIN DBA.performance perf1 ON v1.venue_id = perf1.venue_id
    JOIN DBA.transaction t1 ON perf1.performance_id = t1.performance_id
WHERE contactEMail = 'cheryl.mccarrick@northeastmuseums.org.uk'
    AND eventAccountSettled = 'Y'
    AND performanceDate BETWEEN '2024-04-01 00:00' AND '2025-03-31 23:59'
    AND transactionPaymentMethod IN('STRIPE', 'TS')
    AND transactionCategory IN ('DONATION','SUNDRY','FEE','TICKETS')
GROUP BY pr1.promoter_id, companyName, promoterBookingFeeOption
HAVING Gross > 0
ORDER BY pr1.promoter_id