SQL.txt

========================================================================================
생성
========================================================================================

-- 스키마 생성
CREATE SCHEMA huecalm;

-- 테이블 생성
CREATE TABLE huecalm.hc_channel_sales (
    bp_code NVARCHAR(30) COLLATE Korean_100_CI_AS,
    bp_name NVARCHAR(500) COLLATE Korean_100_CI_AS,
    bp_reg_num NVARCHAR(50) COLLATE Korean_100_CI_AS,
    channel_class NVARCHAR(30) COLLATE Korean_100_CI_AS,
    channel NVARCHAR(30) COLLATE Korean_100_CI_AS,
    channel_click NVARCHAR(30) COLLATE Korean_100_CI_AS,
    channel_manager NVARCHAR(30) COLLATE Korean_100_CI_AS,
    country NVARCHAR(30) COLLATE Korean_100_CI_AS,
);

========================================================================================
주석 달기
========================================================================================

-- sp_addextendedproperty (추가)
-- sp_updateextendedproperty (변경)
-- sp_dropextendedproperty (삭제)

-- 스키마 주석 추가
EXEC wjhjstestsqldatabase.sys.sp_addextendedproperty 'MS_Description', N'휴캄 통합 데이터베이스', 'schema', N'huecalm'

-- 테이블 주석 추가
EXEC wjhjstestsqldatabase.sys.sp_addextendedproperty 'MS_Description', N'휴캄채널코드', 'schema', N'huecalm', 'table', N'hc_channel_sales';

-- 컬럼 주석 추가
EXEC wjhjstestsqldatabase.sys.sp_addextendedproperty 'MS_Description', N'BP코드', 'schema', N'huecalm', 'table', N'hc_channel_sales', 'column', N'bp_code';

========================================================================================
MERGE INTO
========================================================================================

-- MERGE INTO
MERGE INTO silver_sensor_smartwatch_accelerometer_30 OLD USING silver_sensor_smartwatch_accelerometer_30_new NEW
ON OLD.HISTORY_TIME = NEW.HISTORY_TIME AND OLD.SENIOR_ID = NEW.SENIOR_ID AND OLD.HISTORY_TIME >= '${date.start}'
WHEN MATCHED AND OLD.QC_FLAG_RATE IS NULL OR ( OLD.QC_FLAG_RATE IS NOT NULL AND OLD.QC_FLAG_RATE < NEW.QC_FLAG_RATE )
THEN UPDATE SET OLD.HISTORY_TIME = NEW.HISTORY_TIME, 
                OLD.ENRG_ADJ = NEW.ENRG_ADJ,
                OLD.ENRG_ORG = NEW.ENRG_ORG,
                OLD.ENRG_Y_ADJ = NEW.ENRG_Y_ADJ,
                OLD.NA_COUNT = NEW.NA_COUNT,
                OLD.ALL_COUNT = NEW. ALL_COUNT,
                OLD.QC_FLAG_RATE = NEW.QC_FLAG_RATE
WHEN NOT MATCHED 
THEN INSERT ( SENIOR_ID,
              HISTORY_TIME,	
              ENRG_ADJ,
              ENRG_ORG,
              ENRG_Y_ADJ,	
              NA_COUNT,
              ALL_COUNT,	
              QC_FLAG_RATE )
              
VALUES ( NEW.SENIOR_ID, 
         NEW.HISTORY_TIME,
         NEW.ENRG_ADJ,
         NEW.ENRG_ORG,
         NEW.ENRG_Y_ADJ,
         NEW.NA_COUNT,
         NEW.ALL_COUNT,
         NEW.QC_FLAG_RATE )