/
CA_PERDIDAS.SQL
51 lines (49 loc) · 2.43 KB
/
CA_PERDIDAS.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
/********* Begin Procedure Script ************/
BEGIN
var_out =
SELECT DISTINCT sum("NO_PERDIDAS") OVER (PARTITION BY EMPRESA, REGION, CATEGORIA,
ANTIGUEDAD_CLI,
SUBREGION,HUB,CIUDAD,PLAY_CLI, CALMONTH ORDER BY FECHA ASC) AS "NO_PERDIDAS_ACUM",
sum("NO_PERDIDAS") OVER (PARTITION BY EMPRESA, REGION, CATEGORIA,
ANTIGUEDAD_CLI,
SUBREGION,HUB,CIUDAD,PLAY_CLI, CALMONTH,FECHA) AS "NO_PERDIDAS" ,
sum("NO_CTES_FIN_MES") OVER (PARTITION BY EMPRESA, REGION, CATEGORIA,
ANTIGUEDAD_CLI,
SUBREGION,HUB,CIUDAD,PLAY_CLI, CALMONTH,FECHA) AS "NO_CTES_FIN_MES",
"REGION", "CATEGORIA", "ANTIGUEDAD_CLI", "YEAR", "MONTH", "FECHA", "CALMONTH",
"EMPRESA", "DATETIMESTAMP", "SUBREGION", "HUB", "CIUDAD", "PLAY_CLI"
FROM (
SELECT SUM(NO_PERDIDAS) AS NO_PERDIDAS, SUM(NO_CTES_FIN_MES) AS NO_CTES_FIN_MES,
"REGION1" AS REGION, "CATEGORIA1" AS CATEGORIA, "ANTIGUEDAD_CLI", "YEAR",
"MONTH", "FECHA", "CALMONTH",
"EMPRESA", "DATETIMESTAMP", "SUBREGION", "HUB", "CIUDAD", "PLAY_CLI"
FROM "_SYS_BIC"."T.ROAMBI_A/CA_PERDIDAS_RAW"
(PLACEHOLDER."$$IN_CALMONTH_FROM$$" => :IN_CAL_FROM,
PLACEHOLDER."$$IN_CALMONTH_TO$$" => :IN_CAL_TO )
GROUP BY "REGION1", "CATEGORIA1", "ANTIGUEDAD_CLI", "YEAR", "MONTH", "FECHA", "CALMONTH",
"EMPRESA", "DATETIMESTAMP", "SUBREGION", "HUB", "CIUDAD", "PLAY_CLI"
UNION ALL
SELECT 0 AS NO_PERDIDAS, 0 AS NO_CTES_FIN_MES,
"REGION", "CATEGORIA", "ANTIGUEDAD_CLI",
"YEAR", "MONTH", "FECHA", "CALMONTH",
"EMPRESA", "DATETIMESTAMP", "SUBREGION", "HUB", "CIUDAD", "PLAY_CLI"
FROM
( SELECT DISTINCT EMPRESA, REGION1 AS REGION, SUBREGION, HUB, CIUDAD,
CATEGORIA1 AS CATEGORIA, PLAY_CLI, ANTIGUEDAD_CLI
FROM "_SYS_BIC"."T.ROAMBI_A/CA_PERDIDAS_RAW"
(PLACEHOLDER."$$IN_CALMONTH_FROM$$" => :IN_CAL_FROM,
PLACEHOLDER."$$IN_CALMONTH_TO$$" => :IN_CAL_TO )
GROUP BY EMPRESA, REGION1, SUBREGION, HUB, CIUDAD, CATEGORIA1, PLAY_CLI,
ANTIGUEDAD_CLI
) A , (
SELECT DISTINCT YEAR, MONTH, FECHA, CALMONTH, DATETIMESTAMP
FROM "_SYS_BIC"."T.ROAMBI_A/CA_PERDIDAS_RAW"
(PLACEHOLDER."$$IN_CALMONTH_FROM$$" => :IN_CAL_FROM,
PLACEHOLDER."$$IN_CALMONTH_TO$$" => :IN_CAL_TO )
GROUP BY YEAR, MONTH, FECHA, CALMONTH, DATETIMESTAMP
) B
-- GROUP BY "REGION", "CATEGORIA", "ANTIGUEDAD_CLI", "YEAR", "MONTH", "FECHA", "CALMONTH",
-- "EMPRESA", "DATETIMESTAMP", "SUBREGION", "HUB", "CIUDAD", "PLAY_CLI"
)
;
END /********* End Procedure Script ************/