Changes between Initial Version and Version 1 of Ticket #1175, comment 9


Ignore:
Timestamp:
Jun 9, 2023, 10:48:00 AM (18 months ago)
Author:
Denis_N

Legend:

Unmodified
Added
Removed
Modified

  • Ticket #1175, comment 9

    initial v1  
    1 
    2 {{{
    3 drop procedure mismatchQueryWithoutOtkRows;
    4 
    5 DELIMITER }
    6 
    7 CREATE PROCEDURE mismatchQueryWithoutOtkRows(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA
    8 BEGIN
    9 set @prevtotal := 0;
    10 set @mintotal := 0;
    11 select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from
    12 (select products.serial, history.uid, history.date, hist1.total from history inner join
    13 (select uid, count(uid) as total from history where (type_write = "mismatch" or type_write = 'record'
    14 or type_write = "repair" or (type_write = "testing" and status = "fail")) and comment is not null and  comment != '' group by uid) as hist1 on history.uid = hist1.uid
    15 inner join products on products.uid = history.uid where (products.name like searchName
    16 and products.serial like searchSerial and history.comment like searchComment) and (history.uid, history.date) in
    17 (select uid, max(date) from history where (type_write = "mismatch" or type_write = 'record' or type_write = "repair" or
    18 (type_write = "testing" and status = "fail") or `history`.type_write = 'record') and history.comment like searchComment and comment is not null and  comment != '' group by uid)
    19 group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2
    20 where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max;
    21 
    22 END}
    23 DELIMITER ;
    24 
    25 }}}