MICROS RES 3700 How query the time period for a restaurant order

[lrd2022]

Hi, I am very new to SQL queries for MICROS RES 3700. I am hoping that someone can show me the correct way to pull the time period name for a restaurant transaction, for example a breakfast order.

This is the query. It is not pulling the correct time period for all orders, the time period is correct only for some of them. Several dinner orders are showing as lunch.
Is this part of the query incorrect, should the time period be pulled relating to a different table, not micros.trans_dtl, or, are there fields missing from the join in order to select the right time period?:
LEFT JOIN micros.srv_period_def AS P
ON TD.srv_period_seq = P.srv_period_seq

The full query:

SELECT TD.chk_seq CheckID,
'ITEM' CheckType,
Hour(Min(TD.start_date_tm)) CheckTimeHour,
Minute(Min(TD.start_date_tm)) CheckTimeMinute,
P.NAME Period,
CASE WHEN MG.Name = 'Spa' THEN FG.NAME ELSE MG.NAME END Label,
R.NAME CostCenter,
Sum(D.rpt_ttl) Amount
FROM micros.trans_dtl AS TD
INNER JOIN micros.rvc_def AS R
ON TD.rvc_seq = R.rvc_seq
INNER JOIN micros.mi_dtl AS MID
ON TD.trans_seq = MID.trans_seq
INNER JOIN micros.dtl AS D
ON MID.dtl_seq = D.dtl_seq
AND MID.trans_seq = D.trans_seq
INNER JOIN micros.mi_def AS MI
ON MID.mi_seq = MI.mi_seq
INNER JOIN micros.maj_grp_def AS MG
ON MI.maj_grp_seq = MG.maj_grp_seq
INNER JOIN micros.fam_grp_def AS FG
ON MI.fam_grp_seq = FG.fam_grp_seq
LEFT JOIN micros.srv_period_def AS P
ON TD.srv_period_seq = P.srv_period_seq
WHERE TD.business_date = '[yyyy-MM-dd]'
GROUP BY TD.business_date,
TD.chk_seq,
P.NAME,
MG.NAME,
R.NAME
HAVING Sum(D.rpt_ttl) <> 0


Thank you very much. I appreciate any help you can give me on this.

 

[lrd2022]

Hi,

I wanted to clarify that when I said "Time Period" in my previous post, I mean "Service Period".

Thank you!!