Micros 3700 - Coalesce Modifiers with Parent Item 1

[bufftrek]

Has anybody had any experience with a query that coalesces all item modifier to the patent item? I'm not abundantly familiar with the Micros Databasr and have been offered extremely helpful advice here once before on generating unique identifiers for items ran in at various menu levels. Unfortunately, the current client has too many available pour sizes and prove options due to having different pricing schemes for the different bars.

Essentially, when a Jack Daniels($5.50) is rang in and modified as a Double($2.00) with Coke($0.00) for a total of $7.50 I am trying to create a unique item named Jack Daniels Double Coke with 1 sold at $7.50.

Any advice on this would be greatly appreciated!

 

[pmegan]

Give this a try

WITH MI as (
    select 
        M.trans_seq
        ,M.dtl_seq
        ,M.mi_seq
        ,D.rpt_ttl 
        ,D.dtl_name 
    from 
        micros.mi_dtl [M]
        join micros.dtl [D]
            on M.trans_seq = D.trans_seq 
            and M.dtl_seq = D.dtl_seq
    where
        M.ob_is_condiment = 'F'
)
,COND as (
    select 
        M.trans_seq [trans_seq]
        ,M.parent_dtl_seq [dtl_seq]
        ,SUM(D.rpt_ttl) [rpt_ttl]
        ,list(D.dtl_name || ' ' || coalesce(R.ref, '')) [mods]
    from 
        micros.mi_dtl  [M]
        join micros.dtl [D]
            on M.trans_seq = D.trans_seq 
            and M.dtl_seq = D.dtl_seq
        left outer join micros.ref_dtl [R]
            on R.parent_trans_seq = M.trans_seq 
            and R.parent_dtl_seq = M.dtl_seq 
    where
        M.ob_is_condiment = 'T'
    group by 
        M.trans_seq
        ,M.parent_dtl_seq
    order by 
        M.trans_seq     
        ,M.parent_dtl_seq 
)

select 
    MI.trans_seq
    ,MI.dtl_seq 
    ,MI.rpt_ttl + COND.rpt_ttl [total]
    ,MI.dtl_name || ' ' || COND.mods [item]
from 
    MI join COND 
        on MI.trans_seq = COND.trans_seq 
        and MI.dtl_seq = COND.dtl_seq

 

[bufftrek]

Wow, this appears to be getting quite close to what I am trying to acheive but I have to admit that your code is going well beyond my current level of understanding. As of right now it appears that it is only returning items that are modified. Is this the case? For some reason absolutely every item returned is only food as well as opposed to alochol.

 

[pmegan]

Whoops. I just looked at the code and saw that.
Try changing
MI join COND
To
MI left outer join COND

That should include items that aren't modified.

 

[bufftrek]

That does, in fact, include all items but null results are being returned for the combined total price now.

 

[pmegan]

Try this one. The database I had loaded when I did that only had a few items that were all modified. When I changed to the left outer join, the non-modified items were being included, but the prices for those were calculating as [sales price] + NULL. changing that calculation to MI.chk_ttl + coalesce(COND.chk_ttl, 0) sticks zero in for the modifier price whenever there are no mods.

WITH MI as (
    select 
        M.trans_seq
        ,M.dtl_seq
        ,M.mi_seq
        ,D.rpt_ttl 
        ,D.dtl_name 
    from 
        micros.mi_dtl [M]
        join micros.dtl [D]
            on M.trans_seq = D.trans_seq 
            and M.dtl_seq = D.dtl_seq
    where
        M.ob_is_condiment = 'F'
        and D.rpt_cnt <> 0
)
,COND as (
    select 
        M.trans_seq [trans_seq]
        ,M.parent_dtl_seq [dtl_seq]
        ,SUM(D.rpt_ttl) [rpt_ttl]
        ,list(trim(D.dtl_name) || ' ' || coalesce(trim(R.ref), '')) [mods]
    from 
        micros.mi_dtl  [M]
        join micros.dtl [D]
            on M.trans_seq = D.trans_seq 
            and M.dtl_seq = D.dtl_seq
        left outer join micros.ref_dtl [R]
            on R.parent_trans_seq = M.trans_seq 
            and R.parent_dtl_seq = M.dtl_seq 
    where
        M.ob_is_condiment = 'T'
    group by 
        M.trans_seq
        ,M.parent_dtl_seq
    order by 
        M.trans_seq     
        ,M.parent_dtl_seq 
)

select 
    T.business_date
    ,MI.trans_seq
    ,MI.dtl_seq 
    ,MI.rpt_ttl + coalesce(COND.rpt_ttl, 0) [total]
    ,MI.dtl_name || ' ' || COND.mods [item]
from 
    micros.trans_dtl [T]
    join MI 
        on T.trans_seq = MI.trans_seq
    left outer join COND 
        on MI.trans_seq = COND.trans_seq 
        and MI.dtl_seq = COND.dtl_seq
 where 
    T.business_date = today(*) - 1
order by [item]

 

[bufftrek]

Ahhh, that makes perfect sense!
All in all I have been able to make a number of minor adjustments based on the knowledge I did have to bring in a bit more info that I needed but I'm also having some difficulty in locating where the user-supplied text is located when they input a reference for 'Open' items. Do you happen to know where that info is located so that I can also append it as a modifier of sorts?

 

[pmegan]

That will be in the ref_dtl table.

Here's how this is all working.

[ul]
[li]The WITH statement starts the query off by creating common table expressions, (CTE's). They're basically single use temporary tables.[/li]
[li]MI and COND are created as CTE's to store the menu items (MI) and any modifiers (COND) separately. The trans_seq and dtl_seq are used to identify them, and the parent_dtl_seq is used to link the modifiers to the menu item.[/li]
[li]The ref_dtl is joined to the modifiers for the "message" button to pick up typed user entries. It needs the left outer join, otherwise modifiers without any user entry would be filtered out.[/li]
[li]Each modifier name is created by concatenating the actual name and any user entry - trim(D.dtl_name) || ' ' || coalesce(trim(R.ref), ''). The coalesce function is used to keep the modifier from nulling out if there's no reference.[/li]
[li]The COND CTE is grouped by trans_seq and parent_dtl_seq to sum all the modifiers for a menu item. The LIST function is an aggregate function like sum, but it concatenates everything in parenthesis by the grouping. This turns all of the modifiers for a menu item into one long string, and makes it easy to join them together in the final query.[/li]
[/ul]

That being said, you should be able to left outer join ref_dtl to mi_dtl in the MI CTE like I did in COND, and change the dtl_name to concatenate the reference. Just make sure you use coalesce with the reference.

 

[bufftrek]

Pmegan, first of all I wanted to thank you for the help you provided me within this thread and a few others focusing on custom queries. It has helped me to learn a bit more about SQL in an application that I understand!

Needless to say I have slowly modified your provided code above to arrive at something that I thought was working beautifully for my needs until I had a client throw a wrench into the works. They wanted a quantity/num pad on the screen so that they could ring in something like 4 Jack Daniels Rocks as opposed to ringing in Jack Daniels 8 times in a row. My query currently would supply me with the following output:

Jack Daniels Rocks Rocks Rocks Rocks​

So this wouldn't be a problem by just using Distinct in the List function but then 2 more problems come up:
[ol 1]
[li]User Rings in 5 Jack Daniels and selects Rocks, Rocks, Double, Double, and Double as their modifiers returns a total of 5 "Jack Rocks Doubles" sold[/li]
[li]User rings in 1 Grey Goose modified by 2 $Red Bull. Effectively they want twice the normal Red Bull as a mixer but limiting the modifiers returned by distinct would reduce my accounting for how many Red Bulls should have been poured.[/li]
[/ol]

So is there some way that I can split these quantity grouped sales up by the modifiers per each base item?
I understand this might need some clarification as my jargon might not be up to speed - all help is much appreciated as I love expanding my knowledge in this area!!