/
CA_COSECHAS_VENTAS_VS_INVOLUNTARIAS_RGU.SQL
74 lines (66 loc) · 3.86 KB
/
CA_COSECHAS_VENTAS_VS_INVOLUNTARIAS_RGU.SQL
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
/********* Begin Procedure Script ************/
BEGIN
DECLARE CURR_CALMONTH VARCHAR(6);
SELECT "CALMONTH" INTO CURR_CALMONTH
FROM "_SYS_BIC"."T_perf/ATT_TIME_HANA"
WHERE "DATE_SQL" = CURRENT_DATE;
VENTAS =
SELECT sum("VENTAS") AS "VENTAS", "EMPRESA", CASE
WHEN "REGION"='ND' AND EMPRESA='TVI' THEN 'NORESTE'
WHEN "REGION"='ND' AND EMPRESA='CABLEVISION' THEN 'METROPOLITANA'
WHEN "REGION"='ND' AND EMPRESA='CABLEMAS' THEN 'INTERIOR'
ELSE "REGION" END AS "REGION",
COALESCE("SUBREGION",'ND') AS "SUBREGION", COALESCE("HUB",'ND') AS "HUB", COALESCE("CIUDAD",'ND') AS "CIUDAD",
NULL AS "PRODUCTO", "YEAR"*12+"MONTH" AS ANIOMES, "CALMONTH", "CLIENTE", "CATEGORIA"
FROM "_SYS_BIC"."T/ANA_COSECHAS_VENTAS_VS_CANCELACIONES_INVOLUNTARIAS"
WHERE (YEAR(ADD_DAYS(CURRENT_DATE,-1))*12+MONTH(ADD_DAYS(CURRENT_DATE,-1))-"YEAR"*12-"MONTH") BETWEEN 0 AND 9
AND CALMONTH != :CURR_CALMONTH
GROUP BY "EMPRESA", CASE
WHEN "REGION"='ND' AND EMPRESA='TVI' THEN 'NORESTE'
WHEN "REGION"='ND' AND EMPRESA='CABLEVISION' THEN 'METROPOLITANA'
WHEN "REGION"='ND' AND EMPRESA='CABLEMAS' THEN 'INTERIOR'
ELSE "REGION" END, COALESCE("SUBREGION",'ND'), COALESCE("HUB",'ND'), COALESCE("CIUDAD",'ND'), "YEAR", "MONTH", "CALMONTH", "CLIENTE", "CATEGORIA"
HAVING sum("VENTAS")!=0
;
CANCELACIONES =
SELECT DISTINCT "EMPRESA", "YEAR"*12+"MONTH" AS ANIOMES, "CALMONTH", "CLIENTE", "DATE_SQL"
FROM "_SYS_BIC"."T/ANA_COSECHAS_VENTAS_VS_CANCELACIONES_INVOLUNTARIAS"
WHERE NO_CANCELACIONES_INVOLUNTARIAS_CLIENTE IS NOT NULL
AND (YEAR(ADD_DAYS(CURRENT_DATE,-1))*12+MONTH(ADD_DAYS(CURRENT_DATE,-1))-"YEAR"*12-"MONTH") BETWEEN 0 AND 9
AND CALMONTH != :CURR_CALMONTH
;
var_out =
SELECT "EMPRESA", "REGION", "SUBREGION", "HUB", "CIUDAD", "CATEGORIA", PRODUCTO, "CALMONTH" AS MES_VENTAS, NULL AS MES_CANCELACION,
NULL AS TRANSVERSAL, sum("VENTAS") AS "VENTAS", NULL AS CANCELACIONES, NULL AS CANCELACIONES_ACUMULADO
FROM :VENTAS
GROUP BY "EMPRESA", "REGION", "SUBREGION", "HUB", "CIUDAD", "CATEGORIA", PRODUCTO, "CALMONTH"
UNION ALL
SELECT EMPRESA, REGION, SUBREGION, HUB, CIUDAD, CATEGORIA, PRODUCTO, MES_VENTAS, MES_CANCELACION, TRANSVERSAL, NULL, CANCELACIONES,
SUM(CANCELACIONES) OVER (PARTITION BY EMPRESA, REGION, SUBREGION, HUB, CIUDAD, CATEGORIA, PRODUCTO, MES_VENTAS ORDER BY TRANSVERSAL ASC) AS CANCELACIONES_ACUMULADO
FROM(
SELECT EMPRESA, REGION, SUBREGION, HUB, CIUDAD, CATEGORIA, PRODUCTO, MES_VENTAS, MES_CANCELACION, TRANSVERSAL, SUM(VENTAS) AS CANCELACIONES
FROM(
SELECT A.EMPRESA, A.REGION, A.SUBREGION, A.HUB, A.CIUDAD, A.CATEGORIA, A.PRODUCTO, A.CALMONTH AS MES_VENTAS, B.CALMONTH AS MES_CANCELACION, B.ANIOMES-A.ANIOMES AS TRANSVERSAL, A.VENTAS
FROM :VENTAS A INNER JOIN :CANCELACIONES B
ON A.EMPRESA=B.EMPRESA AND A.CLIENTE=B.CLIENTE AND A.ANIOMES<=B.ANIOMES
UNION ALL
SELECT DISTINCT EMPRESA, REGION, SUBREGION, HUB, CIUDAD, CATEGORIA, PRODUCTO, A.CALMONTH AS MES_VENTAS, B.CALMONTH AS MES_CANCELACION,
A.TRANSVERSAL-B.TRANSVERSAL AS TRANSVERSAL, 0 AS VENTAS
FROM(
SELECT DISTINCT "CALMONTH", (YEAR(ADD_DAYS(CURRENT_DATE,-1))*12+MONTH(ADD_DAYS(CURRENT_DATE,-1))-"YEAR"*12-"MONTH") AS TRANSVERSAL
FROM "_SYS_BIC"."T/ATT_TIME_HANA"
WHERE (YEAR(ADD_DAYS(CURRENT_DATE,-1))*12+MONTH(ADD_DAYS(CURRENT_DATE,-1))-"YEAR"*12-"MONTH") BETWEEN 0 AND 10
) A INNER JOIN (
SELECT DISTINCT "CALMONTH", (YEAR(ADD_DAYS(CURRENT_DATE,-1))*12+MONTH(ADD_DAYS(CURRENT_DATE,-1))-"YEAR"*12-"MONTH") AS TRANSVERSAL
FROM "_SYS_BIC"."T/ATT_TIME_HANA"
WHERE (YEAR(ADD_DAYS(CURRENT_DATE,-1))*12+MONTH(ADD_DAYS(CURRENT_DATE,-1))-"YEAR"*12-"MONTH") BETWEEN 0 AND 10
)B
ON A.TRANSVERSAL>=B.TRANSVERSAL
INNER JOIN :VENTAS C
ON A.CALMONTH=C.CALMONTH
)
GROUP BY EMPRESA, REGION, SUBREGION, HUB, CIUDAD, CATEGORIA, PRODUCTO, MES_VENTAS, MES_CANCELACION, TRANSVERSAL
)
WHERE MES_CANCELACION != :CURR_CALMONTH
;
END /********* End Procedure Script ************/