f6LnJNyb

1DROP PROCEDURE IF EXISTS notExistingVat;
2DELIMITER $$
3CREATE PROCEDURE notExistingVat(IN searchYear VARCHAR(255))
4BEGIN
5  SELECT @maxid := max(NUM.numid), @minid := min(NUM.numid) FROM 
6(SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(TBLINV.invoicenum, '/A', 1), ' ', -1) AS UNSIGNED) AS numid 
7FROM tblinvoices as TBLINV
8WHERE TBLINV.invoicenum LIKE CONCAT('%A/', searchYear)) AS NUM;
9DROP TEMPORARY TABLE IF EXISTS IDSeq;
10CREATE TEMPORARY TABLE IF NOT EXISTS IDSeq
11(
12	invoicenum varchar(255)
13);
14SELECT @maxid, @minid;
15
16	WHILE @minid < @maxid DO
17    insert into IDSeq values(CONCAT(CONCAT('VAT ', @minid), CONCAT('/A/', searchYear)));
18    SET @minid := @minid + 1;
19	END WHILE;
20    
21SELECT * FROM IDSeq; 
22
23DROP TEMPORARY TABLE IF EXISTS tempNum;
24CREATE TEMPORARY TABLE IF NOT EXISTS tempNum AS (SELECT invoicenum 
25FROM tblinvoices
26WHERE invoicenum LIKE CONCAT('%A/', searchYear));
27    
28select 
29    s.invoicenum as brakujace
30from 
31    IDSeq s 
32    left join tempNum t on 
33        s.invoicenum = t.invoicenum
34where t.invoicenum is null;
35
36DROP TEMPORARY TABLE IF EXISTS tempNum;
37DROP TEMPORARY TABLE IF EXISTS IDSeq;
38END$$
39DELIMITER ;
40
41CALL notExistingVat('2018');
42DROP PROCEDURE IF EXISTS notExistingVat;