SQL/stored procedure 2 at master · LyaSolis/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
drop PROCEDURE `FINAL`; -- latest all correct

DELIMITER //


CREATE PROCEDURE `FINAL` ()

BEGIN

DECLARE done INT DEFAULT FALSE;

DECLARE V_Id INT DEFAULT 0;

DECLARE cnt INT default 0;

DECLARE v_total float default 0.0;

DECLARE v_total1 float default 0.0;

DECLARE v_total2 float default 0.0;

DECLARE v_total3 float default 0.0;

DECLARE v_total4 float default 0.0;

DECLARE v_total5 float default 0.0;

DECLARE v_total6 float default 0.0;


DECLARE cur1 CURSOR FOR SELECT DISTINCT Id FROM VWTop6 order by id,yearmonth;

DECLARE cur2 CURSOR FOR SELECT Total from VWTop6 where Id=V_id order by id,yearmonth;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


CREATE TABLE IF NOT EXISTS Temp1 (Id INT,Total1 Double,Total2 Double,Total3 Double,Total4 Double,Total5 Double,Total6 Double);


TRUNCATE temp1;


OPEN cur1; /* opens the result set produced by: SELECT DISTINCT Id FROM VWTop6 order by id,yearmonth*/

read_loop1: LOOP /*NAMED loop*/

FETCH cur1 INTO V_Id; /*gets the rows, will fail passed the last row */

IF done THEN

LEAVE read_loop1;

END IF;
	set cnt=0;

set v_total1=0.0;

set v_total2=0.0;

set v_total3=0.0;

set v_total4=0.0;

set v_total5=0.0;

set v_total6=0.0;


OPEN cur2; /*SELECT Total from VWTop6 where Id=V_id*/

read_loop2:LOOP

FETCH cur2 INTO v_total;

IF done THEN

LEAVE read_loop2;

END IF;

set cnt=cnt+1;

CASE cnt

WHEN 1 THEN set v_total1=v_total;

WHEN 2 THEN set v_total2=v_total;

WHEN 3 THEN set v_total3=v_total;

WHEN 4 THEN set v_total4=v_total;

WHEN 5 THEN set v_total5=v_total;

WHEN 6 THEN set v_total6=v_total;

ELSE set v_total6=v_total;

END CASE;

END LOOP;

CLOSE CUR2;

set done =false;

insert into Temp1 (Id,Total1,Total2,Total3,Total4,Total5,Total6) values (V_id,v_total1,v_total2,v_total3,v_total4,v_total5,v_total6);

END LOOP;

CLOSE cur1;

select Id, Total1, Total2, Total3, Total4, Total5, Total6, ROUND(((Total1+Total2+Total3+Total4+Total5+Total6)/6),2) AS Average from temp1;

END//


CALL FINAL();