Oh mighty DBAs hear my plea!
This can't be as complicated as it seems.
If I want to determine the sales of a particular performance as part of a particular package, I write something like this:
select a.perf_no,sum(a.due_amt),sum(a.paid_amt) from t_sub_lineitem a (nolock) join t_perf b (nolock) on a.perf_no = b.perf_nowhere a.pkg_no = ###group by a.perf_no
Since any returns or exchanges generate an additional record in t_sub_lineitem with a negative cash amount, I'm assuming the above query is telling me the total gross sales less all exchanges and returns.
What pieces am I missing? The numbers I get are close to, but not equal to, the numbers on several box office reports. None of the box office reports agree with each other.
Now, I know the box office reports have specifically designed purposes but the folks trying to reconcile with finance are quite perturbed. So, wouldn't the above query be the number that the box office should be handing to finance?
Any thoughts, strategies, additional head-banging-desk comments are appreciated.
Hi Levi,
Good day.
This topic is a bit complicated. Because the link between price type and pkg type is missing. It is DIY time.
I think package counting should be based on the price type level. and it should go to the t_sli_detail level.
because there are no link between price_type and package_type, so you need to setup a lookup table to do this.
it means every price_type should have a package_type. then the count can start from here.
for example, someone buy 1 ticket in 12 pkg price_type, it should regard as 1/12 package sale.
so we can have a custom table (Detail Table) included all sales records, every record should include some new fields:
field 1, tells you (it is package ticket,or single ticket or other ticket, or it is a fee)
field 2, tells you what package type (12, or 10 or 9 .....)
you can schedule this as a overnight job.
also you can create a summary table for a season level or monthly level results.
When you create a report, you can only point to the summary table.
so the report will be very fast.
So I think we should have three custom tables.
1, price type<==> package type lookup table. (we have a LTR_PRICE_TYPE having a field called package_size)
2, sales record detail table.--- run a loop and update every sales records with package info. this is based on your business logic.
3, sales record summary table.
I use these steps to create our daily sales report.
our SQL server keeps a weekly sales detail records, if I use today's record subtract yesterday's sales record, it will be today's sales.
thank you for your time
have fun.
Ben
Ben,
That is an interesting approach and one that certainly gives me some ideas. One thing I'm not clear on though. Is the price_type to package_type relationship 1 to 1? If not, what does that do to the lookup table?
I don't think price_type to package_type will be 1 to 1 relationship. because it should be based on business logic.
I think it should also include the season, we should have a lookup table including pkg_no, price_type, season, and also we add a one more custom field pkg_size.
let's do something interesting.
we can create a LT_SLI_DETAIL_NEW table. we link all sales info and dump them in this table.
in this table detail_sli_no will still be unique.
we can inclue pkg_no, price_type, season, gl_hold_no, season, perf_no, prod_season_no, due_amt, paid_amt, order_no, order_dt, customer_no, sli_status, seat_no, pmap_no, campaign_no... , also can have some identity fields: id, create_date. (basically it just = t_sub_lineitem + t_sli_detail)
it will be an interesting table, because it has everything about sales, except payment.
next step
SELECT a.pkg_no, a.price_type, a.SEASON, b.description FROM LT_SLI_DETAIL_NEW AS a INNER JOIN T_PKG AS b ON a.pkg_no = b.pkg_no GROUP BY a.pkg_no, a.price_type, a.SEASON, b.description ORDER BY a.SEASON, a.pkg_no
OK, now we have a real lookup table need to be finished.
This is reverse engineering.
We can schedule a sql job if any item is not in this table, send out a notice.
So here is my answer for this lookup table:reverse engineering.
We can do this lookup table in an Excel file first, as in our system it has more than 500 records.
and also I should mention, we need put some index on LT_SLI_DETAIL_NEW.
I think this part of work is worth to do it. because if you finish this, you will have a real handy sales table. I have a few custom reports all based on this LT table. (like I have a Sli / Transaction Error Check report, it compares the paid_amt in order with Trn_amt in payment). I cannot share them, because I have a different sales table.