Micros Sales Don't Match? 1

[blurngr]

We're trying to extract sales from a Micros RES 4 database, in 15-minute periods, by revenue center.

More specifically, we are running this type of query against the database:

select count(d.chk_cnt) as count, sum(d.suppressed_rpt_ttl)as sum, tdtl.rvc_seq from trans_dtl as tdtl join dtl as d on tdtl.trans_seq = d.trans_seq join mi_dtl as mdtl on d.trans_seq = mdtl.trans_seq and d.dtl_seq = mdtl.dtl_seq join mi_def as mdef on mdtl.mi_seq = mdef.mi_seq where tdtl.type = 'S' and d.date_time >= '07/01/2012 09:00:00' and d.date_time < '07/01/2012 09:15:00' and tdtl.chk_seq not in ({3}) group by tdtl.rvc_seq;

Where the {3} is a list of any canceled checks, (type "C") during that time period.

Sometimes, the sales numbers match perfectly ... but more often than not, they don't quite match up with the gross sales reports (our numbers are low).

What are we missing?


--
Anthony

 

[pmegan]

Is there a specific reason you're using d.suppressed_rpt_ttl? I'm not really sure what that is; it's one of the new fields in that table and it doesn't seem to always match the reports. I'd try using d.rpt_ttl instead.

 

[blurngr]

Sorry! I didn't get a notification about this response.

Not sure why we are using suppressed_rpt_ttl ... we really know very little about Micros, probably saw this somewhere on the web.

Using the rpt_ttl doesn't necessarily get us any closer, I'm afraid. Two of our revenue centers stay "spot on", two go from being spot on to being off, and one of them that was off, stays the same value.

I'd be happy to share the data for 2 days, we just cannot seem to get it to match up.

--
Anthony

 

[pmegan]

What number are you comparing your totals to? There are tons of totals in Micros and the labels are sometimes misleading.

I just took a better look at your query and tdtl.chk_seq not in ({3}) may be your problem; if not it's certainly contributing to it. Here's what I see happening with the query as written:

[ol]
[li]A server picks up an open check, starts working on it, makes some mistakes and cancels to start over..[/li]
[li]He/she picks up the check again, rings in some items and service totals the check, posting everything to the dtl and tmed_dtl tables.[/li]
[li]Since this check was cancelled, it's chk_seq is included in your cancelled check list, so the valid transaction is filtered out along with the cancelled one, causing your totals to be short by the amount of the valid transaction.[/li]
[/ol]

I'd just get rid of that part of the where clause. Cancelled checks don't carry into the dtl table so it isn't necessary, plus you already have them filtered out by transaction with where tdtl.type = 'S'.

 

[blurngr]

Thanks! So, here's what we have (I've pulled the data from the tables into SQLite and a CSV since I don't have continuous access to their Micros system):
select count( chk_cnt) as count, sum( rpt_ttl) as sum, rvc_seq from micros0718 where date_time >= '19/07/2012' and date_time < '20/07/2012' and type = 'S' and rvc_seq IN (1,2,3,4,5) group by rvc_seq;


Shows:
1907|18049.33|1
21|2723.75|2
1605|3368.95999999999|3
21|7040|4
388|2964.75|5

From here, what we're seeing is that the math should be (based on the reports from Micros):
revenue center 1: Net Sales: 17,310.53, Subtotal Discounts -462.80, "Net Sales" of $17,773.33 != $18,049.33
revenue center 2: Net Sales: 2,723.75, Subtotal Discounts 0.00, "Net Sales" of $2,723.75 == $2,723.75
revenue center 3: Net Sales: 3,145.56, Subtotal Discounts -162.00, "Net Sales" of $3,302.56 != $3,368.96
revenue center 4: Net Sales: 7,040.00, Subtotal Discounts 0.00, "Net Sales" of $7,040.00 == $7,040.00
revenue center 5: Net Sales: 2,599.60, Subtotal Discounts -235.15, "Net Sales" of $2,834.75 != $2,964.75

... I'm still missing something, as three of the revenue centers are not matching, but two of them are.

(In reality, we don't necessarily care about matching net vs. gross sales - just need to get the numbers to add up for at least one of them.)

--
Anthony

 

[blurngr]

By the way ... if we use the suppressed_rpt_ttl ... revenue center's 3 and 5 match up. Very strange.


--
Anthony

 

[pmegan]

Try putting "and rpt_cnt <> 0" into your where clause.

 

[blurngr]

Thanks - you rock! Unfortunately, no dice. Same exact totals.

--
Anthony

 

[pmegan]

Try this. You'll probably have to get access to the database and pull some totals, but it uses the same table, sale_dtl, that posts to the rvc net sales total instead of trying to patch it together from the dtl table. You can use the trans_dtl table to filter times, either with the end_date_tm field or a combination of business_date and fixed_period_seq.

select t.business_date, t.rvc_seq, 
    sum(d.chk_cnt) chk_cnt, sum(s.net_sls_ttl) net_sls_ttl
from micros.trans_dtl t 
join
    (select trans_seq, sum(chk_cnt) chk_cnt
     from micros.dtl 
     where dtl_type = 'M' 
     group by trans_seq) as d
        on t.trans_seq = d.trans_seq
join micros.sale_dtl s
    on t.trans_seq = s.trans_seq
group by t.business_date, t.rvc_seq

 

[blurngr]

No, you really rock!

That query gets us a lot, lot closer. Looking back at 17 days worth of data, the sales match 100% on 8 days. The other 9 days are off slightly from Net Sales ... they are values like $3.75, $-40, $29, $30, etc... IE, they're all rather small.

Any idea what would be missing from this?


--
Anthony

 

[Wildbar]

Do they use different order types? We had an issue with some custom reports in the past where the net sales totals changed when we added an order type....we had to go back and define an order type in the sql calls to get the proper net sales to report.