insurance_complaints/complaint_desc_creation.sql at main · ik-ntim-a/insurance_complaints · GitHub

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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
-- CREATES THE complaints_desc AND complaints_inc TABLE.
-- THE COLUMNS keywords, others_involved, reason_complaint_filed AND how_resolved ARE NOT PRESENT INTO THE  complaint_desc TABLE
-- ALL RECORDS WITH INCOMPLETE VALUES ARE NOT PRESENT IN THE complaint_desc TABLE.

USE insurance_complaints;

-- DELETING ALL CHILD TABLES BEFORE CREATING PARENT.
DROP TABLE IF EXISTS complaint_reason_table;
DROP TABLE IF EXISTS complaint_reason_table_inc;
DROP TABLE IF EXISTS complaint_resolution_table;
DROP TABLE IF EXISTS complaint_resolution_table_inc;

--CREATING PARENTS
DROP TABLE IF EXISTS complaints_desc;
CREATE TABLE complaints_desc
(complaint_no INT NOT NULL,
respondent_id INT NOT NULL,
complaint_filed_against VARCHAR(max) NULL,
complaint_filed_by VARCHAR(max) NULL,
confirmed_complaint VARCHAR(max) NULL,
received_date DATE NULL,
closed_date DATE NULL,
complaint_type VARCHAR(max) NULL,
coverage_type VARCHAR(max) NULL,
coverage_level VARCHAR(max) NULL,
respondent_role VARCHAR(max) NULL,
respondent_type VARCHAR(max) NULL,
complainant_type VARCHAR(max) NULL,
PRIMARY KEY (complaint_no, respondent_id)
);

--TABLE TO CONTAIN INCOMPLETE VALUES
DROP TABLE IF EXISTS complaints_inc
CREATE TABLE complaints_inc
(complaint_no INT NOT NULL,
respondent_id INT NOT NULL,
complaint_filed_against VARCHAR(max) NULL,
complaint_filed_by VARCHAR(max) NULL,
confirmed_complaint VARCHAR(max) NULL,
received_date DATE NULL,
closed_date DATE NULL,
complaint_type VARCHAR(max) NULL,
coverage_type VARCHAR(max) NULL,
coverage_level VARCHAR(max) NULL,
respondent_role VARCHAR(max) NULL,
respondent_type VARCHAR(max) NULL,
complainant_type VARCHAR(max) NULL,
PRIMARY KEY (complaint_no, respondent_id))

-- complaints_desc IS THE DESCRIPTIVE ANALYSIS DATASET.
INSERT INTO insurance_complaints.dbo.complaints_desc
(complaint_no,
respondent_id,
complaint_filed_against,
complaint_filed_by,
confirmed_complaint,
received_date,
closed_date,
complaint_type,
coverage_type,
coverage_level,
respondent_role,
respondent_type,
complainant_type)
SELECT
complaint_no,
respondent_id,
TRIM(complaint_filed_against),
TRIM(complaint_filed_by),
TRIM(confirmed_complaint),
CONVERT(DATE,received_date,101),
CONVERT(DATE,closed_date, 101),
TRIM(complaint_type),
TRIM(coverage_type),
TRIM(coverage_level),
TRIM(respondent_role),
TRIM(respondent_type),
TRIM(complainant_type)
FROM insurance_complaints.dbo.complaints
WHERE
complaint_no IS NOT NULL AND
respondent_id IS NOT NULL AND
complaint_filed_against IS NOT NULL AND
complaint_filed_by IS NOT NULL AND
confirmed_complaint IS NOT NULL AND
received_date IS NOT NULL AND
closed_date IS NOT NULL AND
complaint_type IS NOT NULL AND
coverage_type IS NOT NULL AND
coverage_level IS NOT NULL AND
respondent_role IS NOT NULL AND
respondent_type IS NOT NULL AND
complainant_type IS NOT NULL AND
how_resolved IS NOT NULL AND
reason_complaint_filed IS NOT NULL;

--LOADING DATA INTO complaints_inc dataset
INSERT INTO complaints_inc
(complaint_no,
respondent_id,
complaint_filed_against,
complaint_filed_by,
confirmed_complaint,
received_date,
closed_date,
complaint_type,
coverage_type,
coverage_level,
respondent_role,
respondent_type,
complainant_type)
SELECT
complaint_no,
respondent_id,
TRIM(complaint_filed_against),
TRIM(complaint_filed_by),
TRIM(confirmed_complaint),
CONVERT(DATE,received_date,101),
CONVERT(DATE,closed_date, 101),
TRIM(complaint_type),
TRIM(coverage_type),
TRIM(coverage_level),
TRIM(respondent_role),
TRIM(respondent_type),
TRIM(complainant_type)
FROM insurance_complaints.dbo.complaints
WHERE
complaint_no IS NULL OR
respondent_id IS NULL OR
complaint_filed_against IS NULL OR
complaint_filed_by IS NULL OR
confirmed_complaint IS NULL OR
received_date IS NULL OR
closed_date IS NULL OR
complaint_type IS NULL OR
coverage_type IS NULL OR
coverage_level IS NULL OR
respondent_role IS NULL OR
respondent_type IS NULL OR
complainant_type IS NULL OR
how_resolved IS NULL OR
reason_complaint_filed IS NULL;

-- TABLE OF RECORD COUNTS.
SELECT
(SELECT
COUNT(*)
FROM insurance_complaints.dbo.complaints_desc) AS "complete record count",
(SELECT
COUNT(*)
FROM insurance_complaints.dbo.complaints_inc) AS "incomplete record count",
(SELECT
COUNT(*)
FROM insurance_complaints.dbo.complaints) AS "original dataset record count"