Micros Res3700 Report / SQL

[azrobert]

Hello,

Curious if anyone has written sql to retrieve a list of menu items and the required condiments for each of those items?

 

[goodbody]

This requirement is very common and is usually used to export data reports to third parties.

 

[jvpzoomer]

I'm sure you could. Here is what I wrote to show which modifiers were rung in under which main item:

SELECT DISTINCT
            trans_dtl.business_date AS business_date,
            trans_dtl.chk_seq AS ticket_id,
            vta.C_chk_num AS chk_num,
            dtl.dtl_seq AS dtl_seq,
            mi_dtl.mi_seq AS item_id,
            mi_def.obj_num AS obj_num,
            mi_def.name_1 AS item_name,
            dtl.rpt_cnt AS quantity,
            maj_grp_def.name AS major_category,
            fam_grp_def.name AS minor_category,
            dtl.rpt_ttl AS item_sales
          FROM micros.mi_dtl mi_dtl
          JOIN micros.dtl dtl ON dtl.trans_seq = mi_dtl.trans_seq AND dtl.dtl_seq = mi_dtl.dtl_seq AND dtl.dtl_type = 'M' AND dtl.record_type != 'R' AND dtl.ob_dtl05_void_flag != 'T'
          JOIN micros.mi_def mi_def ON mi_def.mi_seq = mi_dtl.mi_seq
          JOIN micros.maj_grp_def maj_grp_def ON maj_grp_def.maj_grp_seq = mi_def.maj_grp_seq
          JOIN micros.fam_grp_def fam_grp_def ON fam_grp_def.fam_grp_seq = mi_def.fam_grp_seq
          JOIN micros.trans_dtl trans_dtl ON trans_dtl.trans_seq = mi_dtl.trans_seq
          JOIN micros.vta_base_BaseDtl vta ON vta.T_trans_seq = mi_dtl.trans_seq
          WHERE trans_dtl.business_date = DATE(NOW()-1)
          ORDER BY trans_dtl.chk_seq asc, dtl.dtl_seq asc;

 

[azrobert]

Thanks,

I will take a look at that.