id	summary	reporter	owner	description	type	status	priority	component	resolution	keywords	cc
1106	Необходимо создать процедуру в MYSQL для работы ревизии 201	Denis_N	san	"DELIMITER }


CREATE PROCEDURE mismatchQuery(min int, max int, searchName varchar (100), searchSerial varchar (100), searchComment varchar (10000)) READS SQL DATA
        BEGIN
        set @prevtotal := 0;
        set @mintotal := 0;
        select * from (select *, @mintotal as mintotal, @prevtotal := @mintotal - total as prevtotal from
        (select products.serial, history.uid, history.date, hist1.total from history inner join
        (select uid, count(uid) as total from history where (type_write = ""mismatch"" or type_write = ""repair"" or 
        (type_write = ""otk"" and `status` = ""fail"") or (type_write = ""testing"" and `status` = ""fail"")) and `comment` is not null group by uid) as hist1 on history.uid = hist1.uid
        inner join products on products.uid = history.uid where (`products`.`name` like searchName
        and `products`.`serial` like searchSerial and `history`.`comment` like searchComment) and (history.uid, history.date) in (select uid, max(date) from history where (type_write = ""mismatch"" or type_write = ""repair"" or 
        (type_write = ""otk"" and `status` = ""fail"") or (type_write = ""testing"" and `status` = ""fail"")) and `comment` is not null group by uid )
        group by products.serial, history.uid, history.date, hist1.total order by history.date desc) as hist2
        where (@mintotal := @minTotal + total)) as hust2 where hust2.mintotal > min and hust2.prevtotal < max;
        END}

DELIMITER ;"	улучшение	closed	major	БД изделий АДС	готово