The following are queries we have collected during the time we have been using MySQL as a back end. Some of them may be helpful to you.
Go to Mailing list | Generate a mailing list | Export email, then click on the Edit the query tab. Copy and paste the one of the codes below, then click Export list.
*Totals for all sales per hour for a specific date range (This query will help if you need to find a pattern of how busy the box is by hours and days of the week so you can manage box office personnel schedules.)
select hour(today_time) as Hour, sum(total_tix) as Tix, sum(total_sale) as Sales
from mainsale
where today_date >= “20xx-<month>-<date>”
and today_date <= “20xx-<month>-<date>”
group by hour(today_time)
Click here for a screen shot of this report
* What day of the week is the busiest:
select dayname(today_date) as day, sum(total_tix) as tix, sum(total_sale) as sales from mainsale where today_date > ‘2010-10-01’ group by dayofweek(today_date);
* What time do you get your sales:
select hour(today_time) as Hour, sum(total_tix) as Tix, sum(total_sale) as Sales from mainsale group by hour(today_time);
Click here for a screen shot of this report
* What month is the busiest:
select monthname(today_date) as Month, sum(total_tix) as Tix, sum(total_sale) as Sales from mainsale group by month(today_date);
Click here for a screen shot of this report
* What week of the year are you the busiest:
select weekofyear(today_date) as Month, sum(total_tix) as Tix, sum(total_sale) as Sales from mainsale group by weekofyear(today_date);
Click here for a screen shot of this report
* Historical data from Oldsales – by year
select year(today_date) as Year, lpad( format(sum(total_tix),0),6, ‘ ‘) as Tix, lpad(format(sum(total_sale),2),13, ‘ ‘) as Sales from oldsales group by year(today_date) order by today_date;
Click here for a screen shot of this report
* Historical data from Oldsales – by year and month
select year(today_date) as Year, monthname(today_date) as Month, lpad( format(sum(total_tix),0),6, ‘ ‘) as Tix, lpad(format(sum(total_sale),2),13, ‘ ‘) as Sales from oldsales group by month(today_date), year(today_date) order by today_date;
Click here for a screen shot of this report
* All sales within a date range for both mainsale and oldsales
( select master.event_type, show_date, show_time, showname, theatre, location, total_tix, total_sale,
customer.first, customer.last, company, address, customer.city, state, zip, phone, email
from mainsale
left join master on mainsale.master_id=master.master_id
left join customer on mainsale.customer_id=customer.customer_id
where today_date >= ’20XX-XX-XX’ and today_date <= ’20XX-XX-XX’)
union
(select oldmaster.event_type, show_date, show_time, showname, theatre, location, total_tix, total_sale,
customer.first, customer.last, company, address, customer.city, state, zip, phone, email
from oldsales
left join oldmaster on oldmaster.master_id=oldsales.master_id
left join customer on oldsales.customer_id=customer.customer_id
where today_date >= ‘2006-12-05’ and today_date <= ‘2009-12-04’)
order by last, first
* Customers who purchased more than “X” amount of tickets
select count( mainsale_id ) as salecount,
customer.first, customer.last, customer.title, customer.company, customer.address, customer.city, customer.state, customer.zip, customer.phone
from mainsale
left join customer on customer.customer_id=mainsale.customer_id
group by customer.customer_id
having salecount > (enter number of tickets)
order by salecount
Click here to see a screen shot of this report
* Create a zip code report for sales in a specific date range
select
sum(total_tix) as tickets, sum(total_sale) as sales, left(zip,5) as zip
from mainsale
left join customer using(customer_id)
where today_date >= “201x-xx-xx”
and today_date <= “201x-xx-xx”
group by left(zip,5)
order by zip
Click here to see a screen shot of this report
select
customer.first, customer.last, customer.email, customer.notify, customer.notify2, customer.notify3
from customer
where
(customer.notify=1 or customer.notify2=1 or customer.notify3=1) and (customer.email != ”)
order by customer.last, customer.first
Click here to see a screen shot and more info about this report
How to run a report of names entered in the mailing list in Wintix within a certain time frame:
select
customer.last, customer.first, customer.email, customer.entry_date, from customer where customer.email != ” and customer.optin_email_list = 1 and entry_date > “2014-01-01?
order by customer.last, customer.first
Read more about how to run this report.
How to generate a mail list of people who don’t have an email address by running a query
select * from customer where email = “”
How to generate a mail list of people who bought specific shows and don’t have an email address by running a query
select
customer. *
from mainsale
left join customer on customer.customer_id=mainsale.customer_id
left join master on mainsale.master_id=master.master_id
left join shows on mainsale.shows_id=shows.shows_id
After the end parenthesis, add: and customer.email = “”
It will look like this:
where mainstale.master_id in(###, ###) and customer.email = “” The and customer.email = “” will generate the mailing list for those patrons whose email address is blank in the database.
group by customer.customer_id
order by customer.last, customer.first