jZpnU26m

1#This procedure involves calculating PERCENTILE_CONT for file:
2#PROCEDURE_CAPACITY_2HR_UPDATE.sql
3#Dynamic where clause added
4
5DELIMITER //
6DROP PROCEDURE IF EXISTS PERCENTILECONT_RAW2 //
7
8CREATE PROCEDURE PERCENTILECONT_RAW2(IN condition1 VARCHAR(4000))
9
10BEGIN
11
12DROP TABLE IF EXISTS PERCENTILERAW2;
13
14SET @sql_stmt := CONCAT('CREATE TABLE PERCENTILERAW2(FRNVALUE DECIMAL(10,2) DEFAULT NULL,CRNVALUE DECIMAL(10,2) DEFAULT NULL,PERCENTILE_CONT DECIMAL(10,2) DEFAULT NULL) SELECT A2.HOSTNAME, STR_TO_DATE(CONCAT((CAST(DATE_FORMAT(A2.STARTTIME,''%d%m%Y'') AS CHAR)) , '' '' , CAST(CAST(DATE_FORMAT(A2.STARTTIME, ''%H'') AS INTEGER) - MOD(CAST(DATE_FORMAT(A2.STARTTIME,''%H'') AS INTEGER),2) AS CHAR) , '':00:00'') ,''%d%m%Y %H:%i:%s'') AS STARTTIME, A2.MATRIX, CASE WHEN A2.COUNTRY IS NULL THEN '' '' ELSE A2.COUNTRY END AS COUNTRY, A2.MATRIX_VALUE, A2.ROWNUM, B4.RN, B4.CRN, B4.FRN FROM
15	(SELECT 
16        CASE WHEN 
17        (@PREV_HOSTNAME=HOSTNAME 
18            AND @PREV_MATRIX=MATRIX 
19            AND @PREV_STARTTIME=STR_TO_DATE(CONCAT((CAST(DATE_FORMAT(STARTTIME,''%d%m%Y'') AS CHAR)) , '' '' , CAST(CAST(DATE_FORMAT(STARTTIME, ''%H'') AS INTEGER) - MOD(CAST(DATE_FORMAT(STARTTIME,''%H'') AS INTEGER),2) AS CHAR) , '':00:00'') ,''%d%m%Y %H:%i:%s'') 
20            AND @PREV_COUNTRY=COUNTRY ) 
21        THEN @ROWNUM:=@ROWNUM+1 ELSE @ROWNUM:=1 END AS ROWNUM, A1.*, 
22        @PREV_MATRIX:=MATRIX, 
23        @PREV_HOSTNAME:=HOSTNAME, 
24        @PREV_STARTTIME:=STR_TO_DATE(CONCAT((CAST(DATE_FORMAT(STARTTIME,''%d%m%Y'') AS CHAR)) , '' '' , CAST(CAST(DATE_FORMAT(STARTTIME, ''%H'') AS INTEGER) - MOD(CAST(DATE_FORMAT(STARTTIME,''%H'') AS INTEGER),2) AS CHAR) , '':00:00'') ,''%d%m%Y %H:%i:%s'') AS PREV_STARTTIME, 
25        @PREV_COUNTRY:=COUNTRY FROM
26	CAPACITY_RAW A1
27    WHERE MATRIX IS NOT NULL AND MATRIX_VALUE >= 0', condition1 ,' 
28	ORDER BY STR_TO_DATE(CONCAT((CAST(DATE_FORMAT(STARTTIME,''%d%m%Y'') AS CHAR)) , '' '' , CAST(CAST(DATE_FORMAT(STARTTIME, ''%H'') AS INTEGER) - MOD(CAST(DATE_FORMAT(STARTTIME,''%H'') AS INTEGER),2) AS CHAR) , '':00:00'') ,''%d%m%Y %H:%i:%s''),
29	HOSTNAME,CASE WHEN COUNTRY IS NULL THEN '' '' ELSE COUNTRY END, MATRIX, MATRIX_VALUE DESC) A2 
30
31INNER JOIN
32
33(SELECT STARTTIME, HOSTNAME, COUNTRY, MATRIX, RN, CEIL(RN) AS CRN, FLOOR(RN) AS FRN FROM 
34    (SELECT STARTTIME, HOSTNAME, COUNTRY, MATRIX, (1+0.05*(N-1)) AS RN FROM 
35        (SELECT STR_TO_DATE(CONCAT((CAST(DATE_FORMAT(STARTTIME,''%d%m%Y'') AS CHAR)) , '' '' , CAST(CAST(DATE_FORMAT(STARTTIME, ''%H'') AS INTEGER) - MOD(CAST(DATE_FORMAT(STARTTIME,''%H'') AS INTEGER),2) AS CHAR) , '':00:00'') ,''%d%m%Y %H:%i:%s'') as STARTTIME, 
36            HOSTNAME,
37            CASE WHEN COUNTRY IS NULL THEN '' '' ELSE COUNTRY END as COUNTRY, 
38            MATRIX,
39            COUNT(*) AS N 
40        FROM 
41            CAPACITY_RAW B1
42        WHERE 
43            MATRIX IS NOT NULL AND MATRIX_VALUE >= 0', condition1 ,'  
44        GROUP BY 
45            STR_TO_DATE(CONCAT((CAST(DATE_FORMAT(B1.STARTTIME,''%d%m%Y'') AS CHAR)) , '' '' , CAST(CAST(DATE_FORMAT(B1.STARTTIME, ''%H'') AS INTEGER) - MOD(CAST(DATE_FORMAT(B1.STARTTIME,''%H'') AS INTEGER),2) AS CHAR) , '':00:00'') ,''%d%m%Y %H:%i:%s''), B1.HOSTNAME, B1.COUNTRY, B1.MATRIX ORDER BY  STARTTIME, HOSTNAME, COUNTRY, MATRIX) B2) B3) B4 
46        WHERE 
47        A2.HOSTNAME=B4.HOSTNAME AND 
48        STR_TO_DATE(CONCAT((CAST(DATE_FORMAT(A2.STARTTIME,''%d%m%Y'') AS CHAR)) , '' '' , CAST(CAST(DATE_FORMAT(A2.STARTTIME, ''%H'') AS INTEGER) - MOD(CAST(DATE_FORMAT(A2.STARTTIME,''%H'') AS INTEGER),2) AS CHAR) , '':00:00'') ,''%d%m%Y %H:%i:%s'')=B4.STARTTIME AND A2.MATRIX=B4.MATRIX AND A2.COUNTRY=B4.COUNTRY');
49
50PREPARE stmt from @sql_stmt;
51EXECUTE stmt;
52DEALLOCATE PREPARE stmt;
53COMMIT;
54
55UPDATE PERCENTILERAW2 T1 LEFT JOIN PERCENTILERAW2 T2 ON 
56    T1.CRN=T2.ROWNUM AND 
57    T1.HOSTNAME=T2.HOSTNAME AND 
58    T1.MATRIX=T2.MATRIX AND 
59    T1.STARTTIME=T2.STARTTIME AND 
60    T1.COUNTRY=T2.COUNTRY 
61    SET T1.FRNVALUE=T2.MATRIX_VALUE;
62
63UPDATE PERCENTILERAW2 T1 LEFT JOIN PERCENTILERAW2 T2 ON 
64    T1.FRN=T2.ROWNUM AND 
65    T1.HOSTNAME=T2.HOSTNAME AND 
66    T1.MATRIX=T2.MATRIX AND 
67    T1.STARTTIME=T2.STARTTIME AND 
68    T1.COUNTRY=T2.COUNTRY 
69    SET T1.CRNVALUE=T2.MATRIX_VALUE;
70
71UPDATE PERCENTILERAW2 SET PERCENTILE_CONT = CASE WHEN RN=CRN AND RN=FRN THEN MATRIX_VALUE ELSE (((CRN - RN) * CRNVALUE) + ((RN-FRN)* FRNVALUE))  END;
72
73DROP TABLE IF EXISTS PERC_RAW2;
74
75CREATE TABLE PERC_RAW2 SELECT STARTTIME, HOSTNAME, COUNTRY, MATRIX, PERCENTILE_CONT FROM PERCENTILERAW2 GROUP BY STARTTIME, HOSTNAME, COUNTRY, MATRIX;
76
77END
78//
79DELIMITER ;