-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathExtra_script_SQL_SERVER.sql
121 lines (121 loc) · 10.1 KB
/
Extra_script_SQL_SERVER.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
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
IF EXISTS(SELECT 1 FROM sys.views
WHERE Name = 'NPHIES_CLAIMSUPPORTINGINFO')
BEGIN
DROP VIEW dbo.NPHIES_CLAIMSUPPORTINGINFO
END
GO
IF EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='wsl_claim_attachment')
BEGIN
EXECUTE('CREATE VIEW NPHIES_CLAIMSUPPORTINGINFO(PROVCLAIMNO, CATEGORY,SEQUENCENO, REASON,
SUPPORTINGVALUE, SUPPORTINGATTACHMENT, ATTACHMENTFILENAME, ATTACHMENTTYPE, CODE,
TIMINGPERIODFROM, TIMINGPERIODTO,UNIT) AS
SELECT PROVCLAIMNO AS PROVCLAIMNO , Support_Type, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS SEQUENCENO,NULL,SUPPORTINGVALUE, FILECONTENT, FILENAME,
NULL AS NotNeeded_4,CODE,TIMINGPERIODFROM, TIMINGPERIODTO , NULL AS UNIT
FROM
(SELECT PROVCLAIMNO AS PROVCLAIMNO, ''temperature'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),TEMPERATURE) AS SUPPORTINGVALUE, NULL AS FILECONTENT
, NULL FILENAME, NULL AS NotNeeded_4, ''Cel'' As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO , NULL AS UNIT
FROM WSL_GENINFO WHERE TEMPERATURE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''chief-complaint'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, MAINSYMPTOM AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL FILENAME
, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO , NULL AS UNIT
FROM WSL_GENINFO WHERE MAINSYMPTOM IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''vital-sign-weight'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),WEIGH) AS SUPPORTINGVALUE, NULL AS FILECONTENT
, NULL FILENAME, NULL AS NotNeeded_4, ''kg'' As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO , NULL AS UNIT
FROM WSL_GENINFO WHERE WEIGH IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''hospitalized'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4, NULL AS CODE
, NULL, ADMISSIONDATE AS TIMINGPERIODFROM, DISCHARGEDATE AS TIMINGPERIODTO , NULL AS UNIT
FROM WSL_GENINFO WHERE ADMISSIONDATE IS NOT NULL AND DISCHARGEDATE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''pulse'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),PULSE) AS SUPPORTINGVALUE, NULL AS FILECONTENT
, NULL FILENAME, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE PULSE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''respiratory-rate'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),RESPIRATORYRATE) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE RESPIRATORYRATE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''vital-sign-systolic'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),BLOODPRESSURE) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4L, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''vital-sign-diastolic'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),BLOODPRESSURE) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''info'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),RADIOREPORT) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4L, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE RADIOREPORT IS NOT NULL AND CONVERT(VARCHAR,RADIOREPORT) !=''''
UNION ALL
SELECT labResult.PROVCLAIMNO AS PROVCLAIMNO, ''lab-test'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1,
(''LABCODE: '' + ISNULL(labComponent.LABCOMPCODE,'''') +'' ,LABDESC: '' + ISNULL(labComponent.LABCOMPDESC,'''') + '' ,LABRESULT: '' + ISNULL(labComponent.LABRESULT,'''') + '' ,LABRESULTUNIT: ''
+ ISNULL(labComponent.LABRESULTUNIT,'''') + '' ,LABRESULTCOMMENT: '' + ISNULL(labComponent.LABRESULTCOMMENT,'''')) AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4
, labResult.LABTESTCODE AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO , NULL AS UNIT
FROM WSL_LAB_RESULT labResult LEFT JOIN WSL_LAB_COMPONENT labComponent ON labResult.LABTESTCODE=labComponent.LABTESTCODE and labResult.SERIAL=labComponent.SERIAL and labResult.PROVCLAIMNO=labComponent.PROVCLAIMNO
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''last-menstrual-period'', NULL AS SEQUENCENO, NULL, NULL, NULL, NULL, NULL, NULL, LASTMENSTRUATIONPERIOD AS TIMINGPERIODFROM, NULL, NULL AS UNIT
FROM WSL_GENINFO WHERE LASTMENSTRUATIONPERIOD IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''attachment'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1,NULL AS SUPPORTINGVALUE
, FILECONTENT , FILENAME , NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM wsl_claim_attachment WHERE FILENAME IS NOT NULL
) AS SUPPORTING_INFO')
END
ELSE
EXECUTE('CREATE VIEW NPHIES_CLAIMSUPPORTINGINFO(PROVCLAIMNO, CATEGORY,SEQUENCENO, REASON,
SUPPORTINGVALUE, SUPPORTINGATTACHMENT, ATTACHMENTFILENAME, ATTACHMENTTYPE, CODE,
TIMINGPERIODFROM, TIMINGPERIODTO,UNIT) AS
SELECT PROVCLAIMNO AS PROVCLAIMNO , Support_Type, ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS SEQUENCENO,NULL,SUPPORTINGVALUE, FILECONTENT, FILENAME,
NULL AS NotNeeded_4,CODE,TIMINGPERIODFROM, TIMINGPERIODTO , NULL AS UNIT
FROM
(SELECT PROVCLAIMNO AS PROVCLAIMNO, ''temperature'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),TEMPERATURE) AS SUPPORTINGVALUE, NULL AS FILECONTENT
, NULL FILENAME, NULL AS NotNeeded_4, ''Cel'' As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE TEMPERATURE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''chief-complaint'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, MAINSYMPTOM AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL FILENAME
, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE MAINSYMPTOM IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''vital-sign-weight'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),WEIGH) AS SUPPORTINGVALUE, NULL AS FILECONTENT
, NULL FILENAME, NULL AS NotNeeded_4, ''kg'' As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO , NULL AS UNIT
FROM WSL_GENINFO WHERE WEIGH IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''hospitalized'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4, NULL AS CODE
, NULL, ADMISSIONDATE AS TIMINGPERIODFROM, DISCHARGEDATE AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE ADMISSIONDATE IS NOT NULL AND DISCHARGEDATE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''pulse'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),PULSE) AS SUPPORTINGVALUE, NULL AS FILECONTENT
, NULL FILENAME, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE PULSE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''respiratory-rate'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),RESPIRATORYRATE) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE RESPIRATORYRATE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''vital-sign-systolic'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),BLOODPRESSURE) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4L, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''vital-sign-diastolic'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),BLOODPRESSURE) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE BLOODPRESSURE IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''last-menstrual-period'', NULL AS SEQUENCENO, NULL, NULL, NULL, NULL, NULL, NULL, LASTMENSTRUATIONPERIOD AS TIMINGPERIODFROM, NULL
FROM WSL_GENINFO WHERE LASTMENSTRUATIONPERIOD IS NOT NULL
UNION ALL
SELECT PROVCLAIMNO AS PROVCLAIMNO, ''info'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1, CONVERT(NVARCHAR(max),RADIOREPORT) AS SUPPORTINGVALUE
, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4L, NULL As CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_GENINFO WHERE RADIOREPORT IS NOT NULL AND CONVERT(VARCHAR,RADIOREPORT) !=''''
UNION ALL
SELECT labResult.PROVCLAIMNO AS PROVCLAIMNO, ''lab-test'' As Support_Type, NULL AS SEQUENCENO,NULL As NotNeeded_1,
(''LABCODE: '' + ISNULL(labComponent.LABCOMPCODE,'''') +'' ,LABDESC: '' + ISNULL(labComponent.LABCOMPDESC,'''') + '' ,LABRESULT: '' + ISNULL(labComponent.LABRESULT,'''') + '' ,LABRESULTUNIT: ''
+ ISNULL(labComponent.LABRESULTUNIT,'''') + '' ,LABRESULTCOMMENT: '' + ISNULL(labComponent.LABRESULTCOMMENT,'''')) AS SUPPORTINGVALUE, NULL AS FILECONTENT, NULL FILENAME, NULL AS NotNeeded_4
, labResult.LABTESTCODE AS CODE, NULL AS TIMINGPERIODFROM, NULL AS TIMINGPERIODTO, NULL AS UNIT
FROM WSL_LAB_RESULT labResult LEFT JOIN WSL_LAB_COMPONENT labComponent ON labResult.LABTESTCODE=labComponent.LABTESTCODE and labResult.SERIAL=labComponent.SERIAL and labResult.PROVCLAIMNO=labComponent.PROVCLAIMNO
) AS SUPPORTING_INFO')
GO
GO