Micros 9700 (SQL) Report Items Sold, Gross Revenue, Discounts - Including Split Checks.

[osorio82]

Good morning,

I'm fairly new to MicrosDB and trying to create a report out of the Oracle database (micros 9700 version). I need to report Total Items sold, Gross Sales and Discounts per Revenue Center.
I'm having issues with the split checks. I'm not sure how to count the items or the revenue in these cases. I would really appreciate your help on this one.

This is the query I have so far.It think split checks are messing the numbers.

SELECT ch.CHECKID ,
ch.CHECKNUMBER ,
ch.TABLEOPEN ,
ch.CHECKOPEN ,
ch.CHECKCLOSE ,
ch.CHECKPOSTINGTIME ,
ch.STATUS ,
ch.SPLITFROMCHECKNUM ,
ch.SPLITOUT ,
ch.ADDEDTOREVCTRID ,
ch.ADDEDTOCHECKNUM ,
ch.ADDEDIN ,
ch.ORDTYPEID ,
st1.STRINGTEXT menuclass ,
st.STRINGTEXT menu ,
ms.OBJECTNUMBER ,
SUM(c.SALESCOUNT) salescount ,
SUM(CASE WHEN c.DETAILTYPE = 1 THEN c.TOTAL
ELSE 0
END) check_detail_total ,
SUM(CASE WHEN c.DETAILTYPE = 2 THEN c.TOTAL ELSE 0 END) check_discount_total
FROM dbo.CHECK_DETAIL c
LEFT JOIN dbo.MENU_ITEM_DETAIL m ON c.CHECKDETAILID = m.CHECKDETAILID
LEFT JOIN dbo.MENU_ITEM_DEFINITION d ON m.MENUITEMDEFID = d.MENUITEMDEFID
LEFT JOIN dbo.MENU_ITEM_MASTER ms ON d.MENUITEMMASTERID = ms.MENUITEMMASTERID
LEFT JOIN dbo.MENU_ITEM_CLASS mc ON d.MENUITEMCLASSID = mc.MENUITEMCLASSID
LEFT JOIN dbo.MENU_ITEM_PRICE mp ON m.MENUITEMPRICEID = mp.MENUITEMPRICEID
LEFT JOIN dbo.STRING_TABLE st ON ms.NAMEID = st.STRINGNUMBERID
LEFT JOIN dbo.DISCOUNT_DETAIL di ON c.CHECKDETAILID = di.CHECKDETAILID
LEFT JOIN dbo.CHECKS ch ON c.CHECKID = ch.CHECKID
LEFT JOIN dbo.STRING_TABLE st1 ON mc.NAMEID = st1.STRINGNUMBERID
WHERE ch.CHECKPOSTINGTIME >= '2017-06-04 10:43:01.000'
AND ch.CHECKPOSTINGTIME < '2017-06-05 05:24:01.000'
AND c.REVCTRID = 6
AND c.DETAILTYPE IN ( 1,2 )
AND mp.PRICE <> 0
GROUP BY ch.CHECKID ,
ch.CHECKNUMBER ,
ch.STATUS ,
ch.ORDTYPEID ,
st1.STRINGTEXT ,
st.STRINGTEXT ,
ms.OBJECTNUMBER ,
ch.CHECKID ,
ch.CHECKNUMBER ,
ch.TABLEOPEN ,
ch.CHECKOPEN ,
ch.CHECKCLOSE ,
ch.CHECKPOSTINGTIME ,
ch.STATUS ,
ch.SPLITFROMCHECKNUM ,
ch.SPLITOUT ,
ch.ADDEDTOREVCTRID ,
ch.ADDEDTOCHECKNUM ,
ch.ADDEDIN ,
ch.ORDTYPEID ,
st1.STRINGTEXT ,
st.STRINGTEXT ,
ms.OBJECTNUMBER
ORDER BY 1

I would really appreciate your help on this one. Thank you!!

 

[microsGuy16]

You're better off using the totals table for that info. Unless you are looking for actual check detail, i don't think you need to look at these tables. Another advantage to using the totals table is that the data purge is controlled by the system setup, as opposed to the detail tables where it is hard coded to purge after 60 days or so.

I will look up some of my sql files and will get back to you.

 

[osorio82]

Ok. I'm looking at the TOTALS table, do you know what TTLTYPE should I use?
Do you know what the codes mean?

Thank you for all the help. I'll be waiting for those sql files.

 

[osorio82]

Hi microsguy16, do you happen to know the different types of TTLTYPE in the TOTALS table? Or the Flags?

Thank you so much!!!!

 

[microsGuy16]

Sorry for the long delay. I've had some last minute projects come up... Here are two sql files i wrote for MS SQL)

https://www.dropbox.com/sh/rqjg96hl849d7tg/AACCP22FOXNZch8iOfe-aEE3a?dl=0