IF OBJECT_ID ('MVIEW0021_YYYY', 'U') IS NOT NULL DROP TABLE MVIEW0021_YYYY;
GO
CREATE TABLE MVIEW0021_YYYY
(
MPERIOD varchar(8) COLLATE Japanese_BIN NOT NULL
,MKEY1 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY2 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY3 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY4 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY5 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY6 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY7 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY8 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY9 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY10 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY11 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY12 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY13 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY14 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY15 varchar(30) COLLATE Japanese_BIN NOT NULL
,MD1 numeric(14, 2) NOT NULL
,MD2 numeric(14, 2) NOT NULL
,MD3 numeric(14, 2) NOT NULL
,MD4 numeric(14, 2) NOT NULL
,MD5 numeric(14, 2) NOT NULL
,MD6 numeric(14, 2) NOT NULL
,MD7 numeric(14, 2) NOT NULL
,MD8 numeric(14, 2) NOT NULL
,MD9 numeric(14, 2) NOT NULL
,MD10 numeric(14, 2) NOT NULL
,MD11 numeric(14, 2) NOT NULL
,MD12 numeric(14, 2) NOT NULL
,MD13 numeric(14, 2) NOT NULL
,MD14 numeric(14, 2) NOT NULL
,MD15 numeric(14, 2) NOT NULL
,MCNTA numeric(14, 0) NOT NULL
,MCNT1 numeric(14, 0) NOT NULL
,MCNT2 numeric(14, 0) NOT NULL
,MCNT3 numeric(14, 0) NOT NULL
,MCNT4 numeric(14, 0) NOT NULL
,MCNT5 numeric(14, 0) NOT NULL
,MCNT6 numeric(14, 0) NOT NULL
,MCNT7 numeric(14, 0) NOT NULL
,MCNT8 numeric(14, 0) NOT NULL
,MCNT9 numeric(14, 0) NOT NULL
,MCNT10 numeric(14, 0) NOT NULL
,MCNT11 numeric(14, 0) NOT NULL
,MCNT12 numeric(14, 0) NOT NULL
,MCNT13 numeric(14, 0) NOT NULL
,MCNT14 numeric(14, 0) NOT NULL
,MCNT15 numeric(14, 0) NOT NULL
)
GO
CREATE INDEX IDX_MVIEW0021_YYYY_1 ON MVIEW0021_YYYY(MPERIOD);
GO
INSERT INTO MVIEW0021_YYYY
SELECT B.EX_YYYY "MPERIOD"
,A.KEY1 "MKEY1" ,A.KEY2 "MKEY2" ,A.KEY3 "MKEY3" ,A.KEY4 "MKEY4" ,A.KEY5 "MKEY5" ,A.KEY6 "MKEY6" ,A.KEY7 "MKEY7" ,A.KEY8 "MKEY8" ,A.KEY9 "MKEY9" ,A.KEY10 "MKEY10" ,A.KEY11 "MKEY11" ,A.KEY12 "MKEY12" ,A.KEY13 "MKEY13" ,A.KEY14 "MKEY14" ,A.KEY15 "MKEY15"
,SUM(A.D1) "MD1" ,SUM(A.D2) "MD2" ,SUM(A.D3) "MD3" ,SUM(A.D4) "MD4" ,SUM(A.D5) "MD5" ,SUM(A.D6) "MD6" ,SUM(A.D7) "MD7" ,SUM(A.D8) "MD8"
,SUM(A.D9) "MD9" ,SUM(A.D10) "MD10" ,SUM(A.D11) "MD11" ,SUM(A.D12) "MD12" ,SUM(A.D13) "MD13" ,SUM(A.D14) "MD14" ,SUM(A.D15) "MD15"
,COUNT(*) "MCNTA",COUNT(A.D1) "MCNT1",COUNT(A.D2) "MCNT2",COUNT(A.D3) "MCNT3",COUNT(A.D4) "MCNT4",COUNT(A.D5) "MCNT5",COUNT(A.D6) "MCNT6",COUNT(A.D7) "MCNT7",COUNT(A.D8) "MCNT8"
,COUNT(A.D9) "MCNT9",COUNT(A.D10) "MCNT10",COUNT(A.D11) "MCNT11",COUNT(A.D12) "MCNT12",COUNT(A.D13) "MCNT13",COUNT(A.D14) "MCNT14",COUNT(A.D15) "MCNT15"
FROM EX_DATA1 A, EX_DAY B
WHERE A.DATE1 = B.EX_DATE
GROUP BY B.EX_YYYY
,A.KEY1 ,A.KEY2 ,A.KEY3 ,A.KEY4 ,A.KEY5 ,A.KEY6 ,A.KEY7 ,A.KEY8 ,A.KEY9 ,A.KEY10 ,A.KEY11 ,A.KEY12 ,A.KEY13 ,A.KEY14 ,A.KEY15 ;
GO
IF OBJECT_ID ('MVIEW0021_TERM', 'U') IS NOT NULL DROP TABLE MVIEW0021_TERM;
GO
CREATE TABLE MVIEW0021_TERM
(
MPERIOD varchar(8) COLLATE Japanese_BIN NOT NULL
,MKEY1 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY2 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY3 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY4 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY5 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY6 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY7 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY8 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY9 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY10 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY11 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY12 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY13 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY14 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY15 varchar(30) COLLATE Japanese_BIN NOT NULL
,MD1 numeric(14, 2) NOT NULL
,MD2 numeric(14, 2) NOT NULL
,MD3 numeric(14, 2) NOT NULL
,MD4 numeric(14, 2) NOT NULL
,MD5 numeric(14, 2) NOT NULL
,MD6 numeric(14, 2) NOT NULL
,MD7 numeric(14, 2) NOT NULL
,MD8 numeric(14, 2) NOT NULL
,MD9 numeric(14, 2) NOT NULL
,MD10 numeric(14, 2) NOT NULL
,MD11 numeric(14, 2) NOT NULL
,MD12 numeric(14, 2) NOT NULL
,MD13 numeric(14, 2) NOT NULL
,MD14 numeric(14, 2) NOT NULL
,MD15 numeric(14, 2) NOT NULL
,MCNTA numeric(14, 0) NOT NULL
,MCNT1 numeric(14, 0) NOT NULL
,MCNT2 numeric(14, 0) NOT NULL
,MCNT3 numeric(14, 0) NOT NULL
,MCNT4 numeric(14, 0) NOT NULL
,MCNT5 numeric(14, 0) NOT NULL
,MCNT6 numeric(14, 0) NOT NULL
,MCNT7 numeric(14, 0) NOT NULL
,MCNT8 numeric(14, 0) NOT NULL
,MCNT9 numeric(14, 0) NOT NULL
,MCNT10 numeric(14, 0) NOT NULL
,MCNT11 numeric(14, 0) NOT NULL
,MCNT12 numeric(14, 0) NOT NULL
,MCNT13 numeric(14, 0) NOT NULL
,MCNT14 numeric(14, 0) NOT NULL
,MCNT15 numeric(14, 0) NOT NULL
)
GO
CREATE INDEX IDX_MVIEW0021_TERM_1 ON MVIEW0021_TERM(MPERIOD);
GO
INSERT INTO MVIEW0021_TERM
SELECT B.EX_TERM "MPERIOD"
,A.KEY1 "MKEY1" ,A.KEY2 "MKEY2" ,A.KEY3 "MKEY3" ,A.KEY4 "MKEY4" ,A.KEY5 "MKEY5" ,A.KEY6 "MKEY6" ,A.KEY7 "MKEY7" ,A.KEY8 "MKEY8" ,A.KEY9 "MKEY9" ,A.KEY10 "MKEY10" ,A.KEY11 "MKEY11" ,A.KEY12 "MKEY12" ,A.KEY13 "MKEY13" ,A.KEY14 "MKEY14" ,A.KEY15 "MKEY15"
,SUM(A.D1) "MD1" ,SUM(A.D2) "MD2" ,SUM(A.D3) "MD3" ,SUM(A.D4) "MD4" ,SUM(A.D5) "MD5" ,SUM(A.D6) "MD6" ,SUM(A.D7) "MD7" ,SUM(A.D8) "MD8"
,SUM(A.D9) "MD9" ,SUM(A.D10) "MD10" ,SUM(A.D11) "MD11" ,SUM(A.D12) "MD12" ,SUM(A.D13) "MD13" ,SUM(A.D14) "MD14" ,SUM(A.D15) "MD15"
,COUNT(*) "MCNTA",COUNT(A.D1) "MCNT1",COUNT(A.D2) "MCNT2",COUNT(A.D3) "MCNT3",COUNT(A.D4) "MCNT4",COUNT(A.D5) "MCNT5",COUNT(A.D6) "MCNT6",COUNT(A.D7) "MCNT7",COUNT(A.D8) "MCNT8"
,COUNT(A.D9) "MCNT9",COUNT(A.D10) "MCNT10",COUNT(A.D11) "MCNT11",COUNT(A.D12) "MCNT12",COUNT(A.D13) "MCNT13",COUNT(A.D14) "MCNT14",COUNT(A.D15) "MCNT15"
FROM EX_DATA1 A, EX_DAY B
WHERE A.DATE1 = B.EX_DATE
GROUP BY B.EX_TERM
,A.KEY1 ,A.KEY2 ,A.KEY3 ,A.KEY4 ,A.KEY5 ,A.KEY6 ,A.KEY7 ,A.KEY8 ,A.KEY9 ,A.KEY10 ,A.KEY11 ,A.KEY12 ,A.KEY13 ,A.KEY14 ,A.KEY15 ;
GO
IF OBJECT_ID ('MVIEW0021_YYYYMM', 'U') IS NOT NULL DROP TABLE MVIEW0021_YYYYMM;
GO
CREATE TABLE MVIEW0021_YYYYMM
(
MPERIOD varchar(8) COLLATE Japanese_BIN NOT NULL
,MKEY1 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY2 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY3 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY4 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY5 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY6 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY7 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY8 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY9 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY10 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY11 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY12 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY13 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY14 varchar(30) COLLATE Japanese_BIN NOT NULL
,MKEY15 varchar(30) COLLATE Japanese_BIN NOT NULL
,MD1 numeric(14, 2) NOT NULL
,MD2 numeric(14, 2) NOT NULL
,MD3 numeric(14, 2) NOT NULL
,MD4 numeric(14, 2) NOT NULL
,MD5 numeric(14, 2) NOT NULL
,MD6 numeric(14, 2) NOT NULL
,MD7 numeric(14, 2) NOT NULL
,MD8 numeric(14, 2) NOT NULL
,MD9 numeric(14, 2) NOT NULL
,MD10 numeric(14, 2) NOT NULL
,MD11 numeric(14, 2) NOT NULL
,MD12 numeric(14, 2) NOT NULL
,MD13 numeric(14, 2) NOT NULL
,MD14 numeric(14, 2) NOT NULL
,MD15 numeric(14, 2) NOT NULL
,MCNTA numeric(14, 0) NOT NULL
,MCNT1 numeric(14, 0) NOT NULL
,MCNT2 numeric(14, 0) NOT NULL
,MCNT3 numeric(14, 0) NOT NULL
,MCNT4 numeric(14, 0) NOT NULL
,MCNT5 numeric(14, 0) NOT NULL
,MCNT6 numeric(14, 0) NOT NULL
,MCNT7 numeric(14, 0) NOT NULL
,MCNT8 numeric(14, 0) NOT NULL
,MCNT9 numeric(14, 0) NOT NULL
,MCNT10 numeric(14, 0) NOT NULL
,MCNT11 numeric(14, 0) NOT NULL
,MCNT12 numeric(14, 0) NOT NULL
,MCNT13 numeric(14, 0) NOT NULL
,MCNT14 numeric(14, 0) NOT NULL
,MCNT15 numeric(14, 0) NOT NULL
)
GO
CREATE INDEX IDX_MVIEW0021_YYYYMM_1 ON MVIEW0021_YYYYMM(MPERIOD);
GO
INSERT INTO MVIEW0021_YYYYMM
SELECT B.EX_YYYYMM "MPERIOD"
,A.KEY1 "MKEY1" ,A.KEY2 "MKEY2" ,A.KEY3 "MKEY3" ,A.KEY4 "MKEY4" ,A.KEY5 "MKEY5" ,A.KEY6 "MKEY6" ,A.KEY7 "MKEY7" ,A.KEY8 "MKEY8" ,A.KEY9 "MKEY9" ,A.KEY10 "MKEY10" ,A.KEY11 "MKEY11" ,A.KEY12 "MKEY12" ,A.KEY13 "MKEY13" ,A.KEY14 "MKEY14" ,A.KEY15 "MKEY15"
,SUM(A.D1) "MD1" ,SUM(A.D2) "MD2" ,SUM(A.D3) "MD3" ,SUM(A.D4) "MD4" ,SUM(A.D5) "MD5" ,SUM(A.D6) "MD6" ,SUM(A.D7) "MD7" ,SUM(A.D8) "MD8"
,SUM(A.D9) "MD9" ,SUM(A.D10) "MD10" ,SUM(A.D11) "MD11" ,SUM(A.D12) "MD12" ,SUM(A.D13) "MD13" ,SUM(A.D14) "MD14" ,SUM(A.D15) "MD15"
,COUNT(*) "MCNTA",COUNT(A.D1) "MCNT1",COUNT(A.D2) "MCNT2",COUNT(A.D3) "MCNT3",COUNT(A.D4) "MCNT4",COUNT(A.D5) "MCNT5",COUNT(A.D6) "MCNT6",COUNT(A.D7) "MCNT7",COUNT(A.D8) "MCNT8"
,COUNT(A.D9) "MCNT9",COUNT(A.D10) "MCNT10",COUNT(A.D11) "MCNT11",COUNT(A.D12) "MCNT12",COUNT(A.D13) "MCNT13",COUNT(A.D14) "MCNT14",COUNT(A.D15) "MCNT15"
FROM EX_DATA1 A, EX_DAY B
WHERE A.DATE1 = B.EX_DATE
GROUP BY B.EX_YYYYMM
,A.KEY1 ,A.KEY2 ,A.KEY3 ,A.KEY4 ,A.KEY5 ,A.KEY6 ,A.KEY7 ,A.KEY8 ,A.KEY9 ,A.KEY10 ,A.KEY11 ,A.KEY12 ,A.KEY13 ,A.KEY14 ,A.KEY15 ;
GO
