Micros 3700 checkitem totals and check totals dont match.

[jfitchett]

Hello gentlemen.

I have gone from gray'ed haired trying to figure this out. I am simply retrieving check level info and detail level info for each check and expect the sum of charges on the lineitem record to be = to some value in the chk_dtl table, i would expect subtotal. However virtually none of them line up. As a side note in case it matters, at least on menuitem refered to in the mi_dtl table is not in the mi_def.
this is my query:
========================
select chk_dtl.chk_num as CheckID, dtl.dtl_seq as LineItem, isnull(mi_def.obj_num,0) as plu, dtl.rpt_cnt as Quantity, dtl.chk_ttl as SalesValue,
CONVERT(CHAR(23), CONVERT(DATETIME, dtl.date_time, 101), 120) AS CheckItemTime, dtl.seat as SeatID ,ISNULL(mi_dtl.parent_dtl_seq, 0) AS ParentLineItem
,dtl.dtl_name,
SUM(dtl.chk_ttl) OVER() AS SumLineItems,
chk_dtl.sub_ttl AS CheckTotal,
chk_dtl.tax_ttl AS TaxTotal,
chk_dtl.pymnt_ttl AS CheckTotalal
from dtl
join trans_dtl on dtl.trans_seq = trans_dtl.trans_seq
join chk_dtl on trans_dtl.chk_seq = chk_dtl.chk_seq
join mi_dtl on (dtl.trans_seq=mi_dtl.trans_seq and dtl.dtl_seq=mi_dtl.dtl_seq)
left outer join mi_def on mi_dtl.mi_seq = mi_def.mi_seq
where chk_dtl.chk_num = 3291
and dtl_type='M'
order by checkID, Lineitem
====================================================

and gives the following results:
====================================================
CheckID LineIte plu Quantity SalesValue CheckItemTime SeatID ParentLineItem dtl_name SumLineItems CheckTotal TaxTotal CheckTotalal
3291 1 10001 1 8.75 2015-05-17 15:19:52 1 0 CARROT CAKE 158.48 140.98 11.28 152.26
3291 1 5006 1 46.99 2015-05-17 14:08:50 1 0 BRUNCH $ 46.99 158.48 140.98 11.28 152.26
3291 2 5006 1 46.99 2015-05-17 14:08:50 1 0 BRUNCH $ 46.99 158.48 140.98 11.28 152.26
3291 2 10002 1 8.75 2015-05-17 15:19:52 1 0 CHOC MOUSSE 158.48 1410.98 11.28 152.26
3291 3 0 1 11.75 2015-05-17 14:08:50 1 0 ULT ACAI CAIP. 158.48 140.98 11.28 152.26
3291 4 0 1 11.75 2015-05-17 14:08:50 1 0 ULT ACAI CAIP. 158.48 140.98 11.28 152.26
3291 5 0 1 11.75 2015-05-17 14:08:50 1 0 ULT ACAI CAIP. 158.48 140.98 11.28 152.26
3291 6 0 1 11.75 2015-05-17 14:08:50 1 0 ULT ACAI CAIP. 158.48 140.98 11.28 152.26
3291 7 80016 1 0 2015-05-17 14:08:50 1 6 SPEC PREP 158.48 140.98 11.28 152.26

Note the 4 columns on the right included to show chk totals according to chk_dtl. None of them match the 158.48 which the items add up to. So it is hard to reconcile.
the 4 tables involved to make it clear:

==============================================
Mi_dtl:
trans_seq dtl_seq mi_seq obj_num
1229898 5 10953 200115
1229898 7 4292 80016
1229898 1 8611 5006
1229898 3 10953 200115
1230052 1 6408 10001
1230052 2 8643 10002
1229898 2 8611 5006
1229898 4 10953 200115
1229898 6 10953 200115

Dtl:
trans_seq dtl_seq chk_ttl dtl_id dtl_name
1229898 1 46.99 17 BRUNCH $ 46.99
1229898 2 46.99 18 BRUNCH $ 46.99
1229898 3 11.75 19 ULT ACAI CAIP.
1229898 4 11.75 20 ULT ACAI CAIP.
1229898 5 11.75 21 ULT ACAI CAIP.
1229898 6 11.75 22 ULT ACAI CAIP.
1229898 7 0 23 SPEC PREP
1230052 1 8.75 25 CARROT CAKE
1230052 2 8.75 26 CHOC MOUSSE

Trans_dtl:
trans_seq type chk_seq
1229898 S 180769
1230052 S 180769
1230054 S 180769
1230086 S 180769
1230114 S 180769
1230128 S 180769

chk_dtl:
chk_seq chk_num sub_ttl tax_ttl pymnt_ttl
180769 3291 140.98 11.28 152.26

None of the trans amounts add uo to the check and the item ULT ACAI CAIP. is not in the mi_def table at all.

Any pointers would be greatly appreciated.

Thank you

 

[CathalMF]

I just rewrote you sql a bit because you have unnecessary joins.
Your problem is you need to include the PARTITION BY in the OVER() and your joins are a little weird.

select

chk_dtl.chk_num as CheckID,
dtl.dtl_seq as LineItem,
midtl.obj_num as plu,
dtl.rpt_cnt as Quantity,
dtl.chk_ttl as SalesValue,
dtl.date_time as CheckItemTime,
dtl.seat as SeatID,
midtl.parent_dtl_seq as ParentLineItem,
dtl.dtl_name,
SUM(dtl.chk_ttl) OVER(PARTITION BY chk_dtl.chk_num) as SumLineItems,
chk_dtl.sub_ttl as CheckTotal,
chk_dtl.tax_ttl as TaxTotal,
chk_dtl.pymnt_ttl as PaymentTotal
​

FROM micros.dtl dtl

JOIN micros.mi_dtl midtl
on dtl.trans_seq = midtl.trans_seq AND dtl.dtl_seq = midtl.dtl_seq
JOIN micros.trans_dtl tdtl
on tdtl.trans_seq = midtl.trans_seq
JOIN micros.chk_dtl chk_dtl
on tdtl.chk_seq = chk_dtl.chk_seq
​

WHERE chk_dtl.chk_num = 1234

Do you want some custom SIM scripts developed. Contact me via my website

http://www.microspostools.com/

 

[billyjoemc]

I require a POS terminal in large quantity. any recommendations on brand

 

[CathalMF]

@BillyJoeMc - You should create your own thread and give detailed information on what you require in your POS terminals.

Do you want some custom SIM scripts developed. Contact me via my website

http://www.microspostools.com/

 

[jfitchett]

Thank you for the assist. I ran that query and got the same results. The sum is in there solely to keep from using a calculator. The real issue is the the sum of the lineitems does not match the check subtotal. here are the results using that query. Note the sum of salesvalue = 158.48 but the checktotal (subttl) is 140.98 missing 17.50.....

CheckID LineItem plu Quantity SalesValue CheckItemTime SeatID ParentLineItem dtl_name SumLineItems CheckTotal TaxTotal PaymentTotal
3291 5 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 152.26
3291 7 80016 1 0 2015-05-17 14:08:50.000 1 6 SPEC PREP 158.48 140.98 11.28 152.26
3291 1 5006 1 46.99 2015-05-17 14:08:50.000 1 NULL BRUNCH $ 46.99 158.48 140.98 11.28 152.26
3291 3 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 152.26
3291 1 10001 1 8.75 2015-05-17 15:19:52.000 1 NULL CARROT CAKE 158.48 140.98 11.28 152.26
3291 2 10002 1 8.75 2015-05-17 15:19:52.000 1 NULL CHOC MOUSSE 158.48 140.98 11.28 152.26
3291 2 5006 1 46.99 2015-05-17 14:08:50.000 1 NULL BRUNCH $ 46.99 158.48 140.98 11.28 152.26
3291 4 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 152.26
3291 6 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 152.26

 

[pmegan]

try adding

and dtl.chk_cnt <> 0

to the end of your where clause.

 

[hosehead78]

Also, sometimes I've had issues with Inner and Left Joins and some joins in general not bring the correct info over. As if it weren't matching the information correctly so it was passing that column.

 

[jfitchett]

pmegan,

Thank you. I tried this and got same responce, howerver while putting togeher this post show the raw data does nto even match i noticed what appears to be the issue,
If the last two records were not there it totals properly... and their trans_seq number differs.
Dtl:
trans_seq dtl_seq chk_ttl dtl_id dtl_name
1229898 1 46.99 17 BRUNCH $ 46.99
1229898 2 46.99 18 BRUNCH $ 46.99
1229898 3 11.75 19 ULT ACAI CAIP.
1229898 4 11.75 20 ULT ACAI CAIP.
1229898 5 11.75 21 ULT ACAI CAIP.
1229898 6 11.75 22 ULT ACAI CAIP.
1229898 7 0 23 SPEC PREP
1230052 1 8.75 25 CARROT CAKE
1230052 2 8.75 26 CHOC MOUSSE
=====> total: 158.48 <> 140.98 below. ***** howevewr sum of all but last two is

chk_dtl:
chk_seq chk_num sub_ttl tax_ttl pymnt_ttl
180769 3291 140.98 11.28 152.26

So now the question becomes why are those two included in the result set. Well here is how i got there without joins to demonstrate.
First quuery to get check_seq for a particular check no so I can bring in trans_dtl
select chk_seq, chk_num, sub_ttl, tax_ttl, pymnt_ttl from chk_dtl where chk_seq = 180769
which gives:
chk_seq chk_num sub_ttl tax_ttl pymnt_ttl
180769 3291 140.98 11.28 152.26

Now I query trans_dtl to get a trans_seq no the other tables link on:
select trans_seq, type, chk_seq from trans_dtl where chk_seq =180769
which gives:
trans_seq type chk_seq
1229898 S 180769
1230052 S 180769
1230054 S 180769
1230086 S 180769
1230114 S 180769
1230128 S 180769

Then i use those trans_seq since it appears they all relate to that check to get he other two tables
select trans_seq, dtl_seq, chk_ttl, dtl_id, dtl_name from dtl where trans_seq in (1229898,1230052,1230054,1230086,1230114,1230128) and dtl_type='M'---
Which Gives:
trans_seq dtl_seq chk_ttl dtl_id dtl_name
1229898 1 46.99 17 BRUNCH $ 46.99
1229898 2 46.99 18 BRUNCH $ 46.99
1229898 3 11.75 19 ULT ACAI CAIP.
1229898 4 11.75 20 ULT ACAI CAIP.
1229898 5 11.75 21 ULT ACAI CAIP.
1229898 6 11.75 22 ULT ACAI CAIP.
1229898 7 0 23 SPEC PREP
1230052 1 8.75 25 CARROT CAKE
1230052 2 8.75 26 CHOC MOUSSE

So do the bottom two wit diff trans_seq belong to this check and the totals are off or they dont and then why do they get returned...
at leaast a little light at the end of this tunnel

Thanks again. Hope this helps.

 

[jfitchett]

Perhaps another way to skin this cat is to ask is there a report that shows check header and check detail line the receipt would. It should have the correct query to get a check number, line items, and totals that match.

 

[michellemidnight]

New to micros and maybe I'm not following but do you have services charges that need to be included?

select
chk_dtl.chk_num as CheckID,
dtl.dtl_seq as LineItem,
midtl.obj_num as plu,
dtl.rpt_cnt as Quantity,
dtl.chk_ttl as SalesValue,
dtl.date_time as CheckItemTime,
dtl.seat as SeatID,
midtl.parent_dtl_seq as ParentLineItem,
dtl.dtl_name,
SUM(dtl.chk_ttl) OVER(PARTITION BY chk_dtl.chk_num) as SumLineItems,
chk_dtl.sub_ttl as CheckTotal,
chk_dtl.tax_ttl as TaxTotal,
chk_dtl.other_svc_ttl as Svc_Charge,
chk_dtl.pymnt_ttl as PaymentTotal
FROM micros.dtl dtl
JOIN micros.mi_dtl midtl
on dtl.trans_seq = midtl.trans_seq AND dtl.dtl_seq = midtl.dtl_seq
JOIN micros.trans_dtl tdtl
on tdtl.trans_seq = midtl.trans_seq
JOIN micros.chk_dtl chk_dtl
on tdtl.chk_seq = chk_dtl.chk_seq
WHERE date_time >= '05/25/2015'
and chk_dtl.chk_num = '1234'

 

[jfitchett]

Thank you Michelle, What I am trying to do is get two tables exported, the check level data in one, the check detail data in another. But in order to insure we missed nothing an audit is done to check that the detail charges add up to the check total. So using your query I get this. ( I added the trans_seq collumn becuase i find it odd that there are two items with differenct trans_seq numbers than the rest and if you remove those the math works. With all records, line item total is 158.48, check total is 140.90 and without those two lineitems the lineitem total = 140.90 like it should. Are there transactions that affect the check total that are in a table other than the dtl table?

trans_seq CheckID LineItem plu Quantity SalesValue CheckItemTime SeatID ParentLineItem dtl_name SumLineItems CheckTotal TaxTotal Svc_Charge PaymentTotal
1229898 3291 5 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 0 152.26
1229898 3291 7 80016 1 0 2015-05-17 14:08:50.000 1 6 SPEC PREP 158.48 140.98 11.28 0 152.26
1229898 3291 1 5006 1 46.99 2015-05-17 14:08:50.000 1 NULL BRUNCH $ 46.99 158.48 140.98 11.28 0 152.26
1229898 3291 3 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 0 152.26
1230052 3291 1 10001 1 8.75 2015-05-17 15:19:52.000 1 NULL CARROT CAKE 158.48 140.98 11.28 0 152.26
1230052 3291 2 10002 1 8.75 2015-05-17 15:19:52.000 1 NULL CHOC MOUSSE 158.48 140.98 11.28 0 152.26
1229898 3291 2 5006 1 46.99 2015-05-17 14:08:50.000 1 NULL BRUNCH $ 46.99 158.48 140.98 11.28 0 152.26
1229898 3291 4 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 0 152.26
1229898 3291 6 200115 1 11.75 2015-05-17 14:08:50.000 1 NULL ULT ACAI CAIP. 158.48 140.98 11.28 0 152.26

 

[jfitchett]

Wow, I didn't think this would be that tough. I must be explaining it badly since it has to be a common funcion. All i want is the check subtotal and all the line itens that contributed to that total. Much like a check would have to use.

 

[pmegan]

Have you checked to see if those two dessert items were comped, or transferred off the check?

 

[jfitchett]

I'm afraid I would not know how to check that.