Untitled
select tickets.title, tickets.ticket_date, ticket_types.name as type, attendees.time , tickets.quantity_available, CASE WHEN MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time )) IS NOT NULL THEN (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time ) WHEN MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time )) IS NOT NULL THEN (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time ) WHEN MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time )) IS NOT NULL THEN (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time ) ELSE null END AS max_capacity, count(attendees.id) as total_sell, CASE WHEN MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time )) IS NOT NULL THEN (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[0].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[0].time') = attendees.time ) - count(attendees.id) WHEN MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time )) IS NOT NULL THEN (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[1].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[1].time') = attendees.time ) - count(attendees.id) WHEN MAX((SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time )) IS NOT NULL THEN (SELECT CONVERT(JSON_EXTRACT(ts.ticket_session, '$[2].max_capacity'), SIGNED INTEGER) AS max_capacity FROM tickets ts WHERE ts.ticket_session IS NOT NULL AND ts.title = tickets.title AND JSON_EXTRACT(ts.ticket_session, '$[2].time') = attendees.time ) - count(attendees.id) ELSE null END AS tot_available, (count(attendees.id) * tickets.price) as gross_revenue, sum(orders.discount_vendor) as discount_vendor, (count(attendees.id) * tickets.price - sum(orders.discount_vendor)) as net from tickets join ticket_types join attendees join orders on tickets.type = ticket_types.id and attendees.ticket_id = tickets.id and orders.id = attendees.order_id where attendees.is_cancelled = 0 and attendees.is_expired = 0 and tickets.event_id = 3 and tickets.ticket_session IS NOT NULL and attendees.created_at < '2024-03-20 23:59:59' group by tickets.title, tickets.ticket_date, ticket_types.name, attendees.time, tickets.quantity_available, tickets.price;
Leave a Comment