52GQzyPX

1sp:BEGIN
2
3DECLARE c INT;
4DECLARE cc INT;
5DECLARE symbols TEXT;
6
7SET @@group_concat_max_len=16384;
8
9
10CREATE TEMPORARY TABLE temp_lists ENGINE=MEMORY
11SELECT q.symbol, p.`exchange`,  p.type, q.volume, q.last, q.high, q.low, dh.inf_260d_high, dh.inf_260d_low, dh.inf_5d_high, dh.inf_5d_low, dh.inf_20d_high, dh.inf_20d_low,
12       dh.inf_65d_high, dh.inf_65d_low, dh.inf_100d_high, dh.inf_100d_low, dh.inf_ytd_high, dh.inf_ytd_low, f.market_capitalization,
13       `t`.`ytd_available`, `t`.`1m_available`, `t`.`3m_available`, `t`.`6m_available`, `t`.`9m_available`, `t`.`12m_available`
14FROM quoting q, prof p, hist h,  higlow dh, funds f, tech t
15WHERE ( q.symbol = p.symbol
16AND q.symbol = h.symbol
17AND q.symbol = dh.symbol
18AND q.symbol = t.symbol
19AND p.`exchange` IN ('NYSE', 'AMEX', 'NASDAQ', 'OTC-BB', 'OTHER OTC', 'TSX', 'TSX-V')
20AND DATE(q.date) = CURDATE()
21AND h.price_5_close IS NOT NULL
22AND p.type != 11
23AND q.last > .25
24AND q.volume > 1000
25AND q.symbol = f.symbol
26AND q.last < 10000);
27
28
29
30# The rest of the code after this gets repeated ad naseum with different where conditions on temp_lists
31SET c = 0;
32
33SELECT COUNT(*) INTO c FROM temp_lists t 
34    WHERE t.`exchange` IN ('NYSE', 'AMEX', 'NASDAQ')
35AND t.volume > 10000;
36
37if c > 10 THEN
38
39    SET c = 0;
40    SET symbols = NULL;
41
42    SELECT COUNT(*) INTO c FROM temp_lists t WHERE (t.`exchange` IN ('NYSE', 'NASDAQ', 'AMEX') AND t.inf_260d_high IS NOT NULL AND t.high IS NOT NULL AND `t`.`12m_available` = 1);
43    if c > 10 THEN
44        CREATE TEMPORARY TABLE temp_lists2 ENGINE=MEMORY 
45        SELECT t.symbol from temp_lists t
46        WHERE t.`exchange` IN ('NYSE', 'NASDAQ', 'AMEX')
47        AND t.inf_260d_high IS NOT NULL
48        AND t.high IS NOT NULL
49        AND t.high >= t.inf_260d_high
50        AND t.volume > 1000
51        AND t.type != 7
52        AND t.type != 11
53        AND `t`.`12m_available` = 1
54        ORDER BY (t.symbol);
55
56        SELECT COUNT(*) INTO cc FROM temp_lists2 t;
57        SELECT GROUP_CONCAT(t2.symbol) INTO symbols FROM temp_lists2 t2;
58
59        UPDATE symbol_lists l SET l.symbols=IFNULL(symbols, ''), l.lastupdate=CURRENT_TIMESTAMP() WHERE l.listkey='52WKHI_T';
60    END IF;
61
62    DROP TEMPORARY TABLE IF EXISTS temp_lists2;