Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE t(
   gp         VARCHAR(2) NOT NULL
  ,yrmo       INTEGER  NOT NULL
  ,sum_of_ctc INTEGER  NOT NULL
);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',201911,6);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',201912,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202001,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202002,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202003,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202004,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202005,9);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202006,9);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202007,9);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202008,9);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202009,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202010,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202011,8);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202012,10);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202101,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202102,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202103,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202104,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202105,12);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202106,12);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202107,12);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202108,12);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202109,12);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202110,12);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202111,12);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202112,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202201,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202202,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202203,11);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202204,13);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202205,14);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202206,15);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202207,22);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202208,23);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202209,23);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202210,23);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202211,24);
INSERT INTO t(gp,yrmo,sum_of_ctc) VALUES ('B1',202311,27);
select
  coalesce(c.gp, p.gp) as gp,
  coalesce(c.yrmo, p.yrmo + 100) as yrmo_current,
  coalesce(c.sum_of_ctc, 0) as ctc_current,
  coalesce(p.yrmo, c.yrmo - 100) as yrmo_prior,
  coalesce(p.sum_of_ctc, 0) as ctc_prior,
  case when p.sum_of_ctc = 0 or p.sum_of_ctc is null then 1.0
       when c.sum_of_ctc = 0 or c.sum_of_ctc is null then -1.0
       else (1.0 * c.sum_of_ctc - p.sum_of_ctc) / p.sum_of_ctc
  end * 100 as churn_rate
from t c
full join t p on p.gp = c.gp and p.yrmo = c.yrmo - 100
order by c.gp, c.yrmo


GP
YRMO_CURRENT
CTC_CURRENT
YRMO_PRIOR
CTC_PRIOR
CHURN_RATE


B1
201911
6
201811
0
100.000000000000000000

B1
201912
8
201812
0
100.000000000000000000

B1
202001
8
201901
0
100.000000000000000000

B1
202002
8
201902
0
100.000000000000000000

B1
202003
8
201903
0
100.000000000000000000

B1
202004
8
201904
0
100.000000000000000000

B1
202005
9
201905
0
100.000000000000000000

B1
202006
9
201906
0
100.000000000000000000

B1
202007
9
201907
0
100.000000000000000000

B1
202008
9
201908
0
100.000000000000000000

B1
202009
8
201909
0
100.000000000000000000

B1
202010
8
201910
0
100.000000000000000000

B1
202011
8
201911
6
33.333333333333333300

B1
202012
10
201912
8
25.000000000000000000

B1
202101
11
202001
8
37.500000000000000000

B1
202102
11
202002
8
37.500000000000000000

B1
202103
11
202003
8
37.500000000000000000

B1
202104
11
202004
8
37.500000000000000000

B1
202105
12
202005
9
33.333333333333333300

B1
202106
12
202006
9
33.333333333333333300

B1
202107
12
202007
9
33.333333333333333300

B1
202108
12
202008
9
33.333333333333333300

B1
202109
12
202009
8
50.000000000000000000

B1
202110
12
202010
8
50.000000000000000000

B1
202111
12
202011
8
50.000000000000000000

B1
202112
11
202012
10
10.000000000000000000

B1
202201
11
202101
11
0.000000000000000000

B1
202202
11
202102
11
0.000000000000000000

B1
202203
11
202103
11
0.000000000000000000

B1
202204
13
202104
11
18.181818181818181800

B1
202205
14
202105
12
16.666666666666666600

B1
202206
15
202106
12
25.000000000000000000

B1
202207
22
202107
12
83.333333333333333300

B1
202208
23
202108
12
91.666666666666666600

B1
202209
23
202109
12
91.666666666666666600

B1
202210
23
202110
12
91.666666666666666600

B1
202211
24
202111
12
100.000000000000000000

B1
202311
27
202211
24
12.500000000000000000

B1
202310
0
202210
23
-100.000000000000000000

B1
202411
0
202311
27
-100.000000000000000000

B1
202306
0
202206
15
-100.000000000000000000

B1
202303
0
202203
11
-100.000000000000000000

B1
202212
0
202112
11
-100.000000000000000000

B1
202308
0
202208
23
-100.000000000000000000

B1
202304
0
202204
13
-100.000000000000000000

B1
202301
0
202201
11
-100.000000000000000000

B1
202302
0
202202
11
-100.000000000000000000

B1
202307
0
202207
22
-100.000000000000000000

B1
202309
0
202209
23
-100.000000000000000000

B1
202305
0
202205
14
-100.000000000000000000

GP	YRMO_CURRENT	CTC_CURRENT	YRMO_PRIOR	CTC_PRIOR	CHURN_RATE
B1	201911	6	201811	0	100.000000000000000000
B1	201912	8	201812	0	100.000000000000000000
B1	202001	8	201901	0	100.000000000000000000
B1	202002	8	201902	0	100.000000000000000000
B1	202003	8	201903	0	100.000000000000000000
B1	202004	8	201904	0	100.000000000000000000
B1	202005	9	201905	0	100.000000000000000000
B1	202006	9	201906	0	100.000000000000000000
B1	202007	9	201907	0	100.000000000000000000
B1	202008	9	201908	0	100.000000000000000000
B1	202009	8	201909	0	100.000000000000000000
B1	202010	8	201910	0	100.000000000000000000
B1	202011	8	201911	6	33.333333333333333300
B1	202012	10	201912	8	25.000000000000000000
B1	202101	11	202001	8	37.500000000000000000
B1	202102	11	202002	8	37.500000000000000000
B1	202103	11	202003	8	37.500000000000000000
B1	202104	11	202004	8	37.500000000000000000
B1	202105	12	202005	9	33.333333333333333300
B1	202106	12	202006	9	33.333333333333333300
B1	202107	12	202007	9	33.333333333333333300
B1	202108	12	202008	9	33.333333333333333300
B1	202109	12	202009	8	50.000000000000000000
B1	202110	12	202010	8	50.000000000000000000
B1	202111	12	202011	8	50.000000000000000000
B1	202112	11	202012	10	10.000000000000000000
B1	202201	11	202101	11	0.000000000000000000
B1	202202	11	202102	11	0.000000000000000000
B1	202203	11	202103	11	0.000000000000000000
B1	202204	13	202104	11	18.181818181818181800
B1	202205	14	202105	12	16.666666666666666600
B1	202206	15	202106	12	25.000000000000000000
B1	202207	22	202107	12	83.333333333333333300
B1	202208	23	202108	12	91.666666666666666600
B1	202209	23	202109	12	91.666666666666666600
B1	202210	23	202110	12	91.666666666666666600
B1	202211	24	202111	12	100.000000000000000000
B1	202311	27	202211	24	12.500000000000000000
B1	202310	0	202210	23	-100.000000000000000000
B1	202411	0	202311	27	-100.000000000000000000
B1	202306	0	202206	15	-100.000000000000000000
B1	202303	0	202203	11	-100.000000000000000000
B1	202212	0	202112	11	-100.000000000000000000
B1	202308	0	202208	23	-100.000000000000000000
B1	202304	0	202204	13	-100.000000000000000000
B1	202301	0	202201	11	-100.000000000000000000
B1	202302	0	202202	11	-100.000000000000000000
B1	202307	0	202207	22	-100.000000000000000000
B1	202309	0	202209	23	-100.000000000000000000
B1	202305	0	202205	14	-100.000000000000000000