gd7ziSXb

1
2/*!
3      @insert
4      @table: @transform_schema.lk_care_site
5      @source_table: @staging_schema.encounters
6      @summary: Extraction of records from encounters
7!*/
8CREATE TABLE  @cdm_schema.tmp_care_site AS
9    SELECT
10        src.enc_dep_nm                      AS care_site_source_value,
11        'Rule1.geisinger_pidb.encounters'   AS unit_id,
12        'geisinger_pidb.encounters'         AS load_table_id,
13        MIN(src.load_row_id)                AS load_row_id
14    FROM
15        @staging_schema.encounters src
16    WHERE
17        src.enc_dep_nm IS NOT NULL
18    GROUP BY
19        src.enc_dep_nm
20;
21
22/*!
23      @insert
24      @table: @transform_schema.lk_care_site
25      @source_table: @staging_schema.hospital_adt
26      @summary: Extraction of records from hospital_adt, (которых нет в таблице encounters)
27!*/
28INSERT INTO @cdm_schema.tmp_care_site
29    SELECT
30        src.dep_nm                          AS care_site_source_value,
31        'Rule1.geisinger_pidb.hospital_adt' AS unit_id,
32        'geisinger_pidb.hospital_adt'       AS load_table_id,
33        MIN(src.load_row_id)                AS load_row_id
34    FROM
35        @staging_schema.hospital_adt src
36    LEFT JOIN 
37        @cdm_schema.tmp_care_site tcs
38            ON tcs.care_site_source_value = src.dep_nm
39    WHERE
40        src.dep_nm IS NOT NULL
41        AND tcs.care_site_source_value IS NULL
42    GROUP BY
43        src.dep_nm
44;
45
46
47--============================================================
48-- Insert care sites to CDM table
49--============================================================
50/*!
51      @insert
52      @table: @cdm_schema.care_site
53      @source_table: @transform_schema.lk_care_site
54      @summary: Population of CDM Care site table
55!*/
56
57INSERT INTO @cdm_schema.care_site
58    SELECT
59        monotonically_increasing_id() + 1   AS care_site_id,
60        care_site_source_value              AS care_site_name,
61        0                                   AS place_of_service_concept_id,
62        NULL                                AS location_id,
63        care_site_source_value              AS care_site_source_value,
64        NULL                                AS place_of_service_source_value,
65        unit_id                             AS unit_id,
66        load_table_id                       AS load_table_id,
67        load_row_id                         AS load_row_id
68    FROM
69        @transform_schema.tmp_care_site
70;
71
72
73DROP TABLE IF EXISTS @transform_schema.tmp_care_site PURGE;
74
75--============================================================