Micros menu item movement relations 1

[Moregelen]

Alright.. So I know enough sql that I can always figure out some way to get what I want out of the database, even if it's not pretty. For this, we have a customer (international farmers market) who already have this report on their retail system. Now they want it on the Micros system. Essentially the report lists all of the items sold with other menu items, and how many.

This is the sql I came up with:

CREATE TABLE #temp_values (
    key_seq SEQ_NUM,
    key_name NAME16,
    mi_seq SEQ_NUM,
    name_1 NAME16,
    qty INTEGER
)
 
DECLARE @mi_key SEQ_NUM
DECLARE @name_1 NAME16
SELECT mi_seq,name_1 INTO #temp_mi_seq FROM micros.mi_def
 
WHILE EXISTS(SELECT 1 FROM #temp_mi_seq)
BEGIN
    SET ROWCOUNT 1
    SELECT @mi_key = mi_seq, @name_1 = name_1 FROM #temp_mi_seq
    SET ROWCOUNT 0
    DELETE FROM #temp_mi_seq WHERE mi_seq = @mi_key
 
    INSERT INTO #temp_values
    SELECT
        @mi_key AS key_seq,
        @name_1 AS key_name,
        midtl.mi_seq,
        midef.name_1,
        COUNT(midtl.mi_seq) AS qty
    INTO #temp_values
    FROM micros.mi_dtl midtl
    JOIN micros.mi_def midef
    ON midef.mi_seq = midtl.mi_seq
    JOIN micros.trans_dtl tdtl
    ON tdtl.trans_seq = midtl.trans_seq
    WHERE midtl.trans_seq IN(
            SELECT
                i.trans_seq
            FROM
                micros.mi_dtl i
            WHERE
                i.mi_seq = key_seq)
        AND midtl.mi_seq <> key_seq
        AND tdtl.business_date BETWEEN '2013/09/7' AND '2013/09/27'
    GROUP BY
        key_seq,
        midtl.mi_seq,
        midef.name_1
    ORDER BY
        qty DESC,
        name_1
 
END
 
SELECT * FROM #temp_values;

Thing is that it can take upwards of 40 seconds or more just to run the query, depending on how many menu items they select and the date range they select. Any sql gurus out there know a better, more efficient ways to do this?

 

[pmegan]

Some of the query is off a bit, and I'm a little confused on what the results are going to be.

This part should fail. You can't have two inserts on the same query, and the "INTO #temp_values" after the SELECT will try to create a table that already exists.

INSERT INTO #temp_values
SELECT
@mi_key AS key_seq,
@name_1 AS key_name,
midtl.mi_seq,
midef.name_1,
COUNT(midtl.mi_seq) AS qty
INTO #temp_values
...

Also, the COUNT(midtl.mi_seq) is going to understate the quantity if multiples of the same item are rung, (2 pumpkin instead of 1 pumpkin, 1 pumpkin).


The query is kind of tough to follow. It looks like the query is supposed to return the mi_seq and name of each menu item in mi_def, along with the mi_seq, name and quantity of each item that was sold alongside them, is that about right? Basically a list of each item sold, without a quantity, and the number, name and quantity of each item sold with it? And it's a date range summary, not by transaction?

So with these two checks:

2 pumpkin
1 scarecrow
3 jelly

and

1 jelly
3 pumpkin
2 apples

you'd get back

item# pumpkin item# scarecrow 1
item# pumpkin item# jelly 4
item# pumpkin item# apples 2
item# scarecrow item# pumpkin 2
item# scarecrow item# jelly 3
item# jelly item# pumpkin 5
item# jelly item# scarecrow 1
item# jelly item# apples 2
item# apples item# jelly 1
item# apples item# pumpkin 3

 

[pmegan]

I ran the query on my test server with a live restaurant backup db. That loop is a beast. The query took 319 seconds and returned 19,216 rows.

Give this one a shot; it ran on my system in under a second. The first 4 columns were identical to the results from your query so the items are matching correctly, but the counts differed on some lines; sometimes mine was higher, other times lower. That's probably from the COUNT(midtl.mi_seq) missing multiples and showing low, and also the way that micros posts voids to mi_dtl twice making it show high. I entered a few transactions as a controlled test and they came out identical using both queries.

Oh, and I noticed you had a 20 day range, these will only run for 14 days back. Beyond that the data from the detail tables is purged already.

-- sum item quantities per transaction and save in #t_sales
select
    trans.trans_seq
    ,mdtl.mi_seq
    ,sum(dtl.chk_cnt) [qty]
into #t_sales
from 
    micros.trans_dtl [trans]
    join micros.mi_dtl [mdtl]
        on trans.trans_seq = mdtl.trans_seq
    join micros.dtl [dtl]
        on mdtl.trans_seq = dtl.trans_seq
        and mdtl.dtl_seq = dtl.dtl_seq
where trans.business_date between '2013/09/7' AND '2013/09/27'
group by
    trans.trans_seq
    ,mdtl.mi_seq;



Select 
    VFinal.key_seq
    ,mkey.name_1 [key_name]
    ,VFinal.mi_seq
    ,mi.name_1 [mi_name]
    ,VFinal.qty
from (
        /* join #t_sales to itself on trans_seq where mi_seq is different
           summing up sales quantity for secondary items
        */
        Select 
            Vkey.mi_seq [key_seq]
            ,Vmi.mi_seq [mi_seq]
            ,sum(Vmi.qty) [qty] 
        from 
            (select trans_seq, mi_seq from #t_sales) as Vkey
            join (select distinct trans_seq, mi_seq, qty from #t_sales) as Vmi
                on Vkey.trans_seq = Vmi.trans_seq
                and Vkey.mi_seq <> Vmi.mi_seq
        group by 
            Vkey.mi_seq
            ,Vmi.mi_seq
    ) as VFinal
    -- link mi_def to key items
    join micros.mi_def mkey
        on VFinal.key_seq = mkey.mi_seq
    -- link mi_def to summary items
    join micros.mi_def mi
        on VFinal.mi_seq = mi.mi_seq
order by
    key_seq
    ,mi.mi_seq

 

[Moregelen]

Thanks pmegan! I'll take a look at this. It was an odd request...

 

[Moregelen]

Hmmm. I really do need to change the way I think about SQL; I've used it for a long time but the stuff I did with it before was in web development on MySQL, so no where near this complex. Thanks pmegan! Reading through that really helps me, even beyond the immediate needs. I should probably hunker down and read an SQL book...

 

[pmegan]

Yeah, approaching SQL like application programming can result in some pretty inefficient code. You have to think in datasets. Loops and iterations will kill you.

This might work, depending on the version of 3700 that you're running it on. It uses a CTE instead of a temp table, so it's less expensive and you won't get any "table already exists" errors if you run it multiple times in the same session. I got rid of the sub-sub queries inside the VFinal virtual table definition too.

-- sum item quantities per transaction in CTE TSales
WITH TSales as (
select
    trans.trans_seq
    ,mdtl.mi_seq
    ,sum(dtl.rpt_cnt) [qty]
from 
    micros.trans_dtl [trans]
    join micros.mi_dtl [mdtl]
        on trans.trans_seq = mdtl.trans_seq
    join micros.dtl [dtl]
        on mdtl.trans_seq = dtl.trans_seq
        and mdtl.dtl_seq = dtl.dtl_seq
--where trans.business_date between dateadd(day, -5, today(*)) and today(*)
group by
    trans.trans_seq
    ,mdtl.mi_seq
)

Select 
    VFinal.key_seq
    ,mkey.name_1 [key_name]
    ,VFinal.mi_seq
    ,mi.name_1 [mi_name]
    ,VFinal.qty
from (
        /* join #t_sales to itself on trans_seq where mi_seq is different
           summing up sales quantity for secondary items
        */
        Select 
            Vkey.mi_seq [key_seq]
            ,Vmi.mi_seq [mi_seq]
            ,sum(Vmi.qty) [qty] 
        from 
            TSales as Vkey
            join TSales as Vmi
                on Vkey.trans_seq = Vmi.trans_seq
                and Vkey.mi_seq <> Vmi.mi_seq
        group by 
            Vkey.mi_seq
            ,Vmi.mi_seq
    ) as VFinal
    -- link mi_def to key items
    join micros.mi_def mkey
        on VFinal.key_seq = mkey.mi_seq
    -- link mi_def to summary items
    join micros.mi_def mi
        on VFinal.mi_seq = mi.mi_seq
order by
    key_seq
    ,mi.mi_seq

 

[Moregelen]

Oh yeah.. definitely have to pick up a book on SQL since I had no idea what you were doing there without googling...

 

[pmegan]

CTE's are just result sets that live in the scope of the query. They don't have to get stored as a database object so there's really low overhead. The one in this query sums up the menu items that were actually sold. Using this as the base of the actual query streamlines it by filtering out irrelevant items.

The grunt work is inside the VFinal inline table. It joins the TSales CTE to itself by transaction sequence for non-like menu items. If you did a select * you'd get sequence numbers and counts for both sides, but by only selecting the two seqs and summing the second you get each menu item sold and the total count for each item sold in the same transactions. The outer joins to mi_def are just to get the names. Doing that outside of the VFinal def lets us keep the names out of the group by clause; filtering and grouping on text is way slower than using just integers.

 

[Moregelen]

Thanks for the information pmegan. Unfortunately the one guy in our office who used to maintain all of the custom reports for our customers has moved on to another company, and it looks like they are angling to dump it all on me.... guess I'm going to need to learn quite a bit more SQL than I currently have under my belt. At least Crystal reports, so far at least, is pretty straight forward.