Hello hive mind!
My Finance Director wants a break down of transaction totals by payment method for a particular production season with just ticket values (no fees). I created a dashboard that seems to look right, but I can't for the life of me get it to match up with what I have on a box office statement or ticket sales by period report. I've tried pulling info from the database, but I'm not having much luck there either (granted, not super experienced with that, but I'm happy to share the sql I've used to get iffy results if someone has an idea on how to help).
Any suggestions? My numbers are off by about $7k. My dash file is attached.
Here are the criteria I used for the Ticket Sales By Period report (those numbers match my box office statement):
Here is my sql if anyone is interested in that as well (I couldn't get these numbers to match up either):
SELECT [impresario].[dbo].[T_ORDER].[order_no] ,[impresario].[dbo].[T_ORDER].[appeal_no] ,[impresario].[dbo].[T_ORDER].[source_no] ,[impresario].[dbo].[T_ORDER].[customer_no] ,[impresario].[dbo].[T_ORDER].[create_dt] ,[impresario].[dbo].[T_ORDER].[tot_ticket_purch_amt] ,[impresario].[dbo].[T_ORDER].[tot_ticket_return_amt] ,[impresario].[dbo].[T_ORDER].[tot_fee_amt] ,[impresario].[dbo].[T_ORDER].[tot_ticket_paid_amt] ,[impresario].[dbo].[T_ORDER].[tot_fee_paid_amt] ,[impresario].[dbo].[T_ORDER].[transaction_no] ,[impresario].[dbo].[T_PAYMENT].[customer_no] ,[impresario].[dbo].[T_PAYMENT].[pmt_dt] ,[impresario].[dbo].[T_PAYMENT].[pmt_amt] ,[impresario].[dbo].[T_PAYMENT].[pmt_method] ,[impresario].[dbo].[T_PAYMENT].[create_dt] ,[impresario].[dbo].[T_TRANSACTION].[create_dt] ,[impresario].[dbo].[T_TRANSACTION].[order_no] ,[impresario].[dbo].[T_TRANSACTION].[perf_no] FROM [impresario].[dbo].[T_ORDER] LEFT JOIN [impresario].[dbo].[T_PAYMENT] ON [impresario].[dbo].[T_ORDER].[create_dt] = [impresario].[dbo].[T_PAYMENT].[create_dt] LEFT JOIN [impresario].[dbo].[T_TRANSACTION] ON [impresario].[dbo].[T_ORDER].[order_no] = [impresario].[dbo].[T_TRANSACTION].[order_no] WHERE [perf_no] = 400 OR [perf_no] = 406 OR [perf_no] = 403
DKPaymentMethodBreakdown.dash..dash..dash
Hi Jordan,
Just a start for the SQL
Try joining T_PAYMENT on T_TRANSACTION on Sequence_no
The problem being that T_PAYMENT doesn't have PERF_NO to filter by and you'll have other perfs booked in the order bleeding through (eg: subs)
however T_TRANSACTION and T_PAYMENT are joined at the sequence_no and you'll get a straight one to one from this link
Cheers,
H
Thank you Heath! I've got the how to do things technically well underway, but I'm still learning the best way of doing things/why you would do something one way rather than another, so that was super helpful! Things are running super slowly here today, but it seems to have helped clear things up a bit.