How do I update a type 2 dimension table with some type 1 columns with the following requirement --If the newest entry from stage data is blank/NULL and previously a value existed-- do not update, only update if there is a different value and at the same time PREVENT the target table from inserting an additional row due to identifying a different/new value from stage (null/blank) been that current data in dimension is not null).
CREATE TABLE [dim].[patient](
[dim_patient_current_de_key] [int] IDENTITY(1,1) NOT NULL,
[id] [varchar](20) NULL,
[client] [varchar](40) NULL,
[DOB] [datetime] NULL,
[Gender_Identity_code] [varchar](50) NULL,--type 2
[dss_load_date] [datetime] NULL,
[dss_start_date] [datetime] NULL,
[dss_end_date] [datetime] NULL,
[dss_current_flag] [char](1) NULL,
[dss_version] [int] NULL,
[dss_create_time] [datetime] NULL,
[dss_update_time] [datetime] NULL,
[StateID_number] [varchar](240) NULL,
CONSTRAINT [dim_patient_demographi_idx_0] PRIMARY KEY CLUSTERED
-------- where the update (are for type 1 columns)
UPDATE [dim].[dim_patient] WITH ( TABLOCK )
SET client = changes.client_name
, DOB = changes.DOB
, SSN = changes.SSN
, StateID_number = CASE
WHEN NULLIF(changes.StateID_number, '') IS NULL THEN [TABLEOWNER].[dim_patient].StateID_number
ELSE changes.StateID_number
END
, dss_update_time = @v_current_datetime
FROM
(
SELECT stage_patient.id id
, stage_patient.client client
, stage_patient.DOB DOB
, stage_patient.Gender_Identity_code
, stage_patient.StateID_number StateID_number
FROM [stage].[stage_patient] stage_patient
EXCEPT
SELECT dim_patient.id id
, dim_patient.client client
, dim_patient.DOB DOB
, dim_patient.Gender_Identity_code
, dim_patient.StateID_number StateID_number
FROM [dim].[dim_patient]
WHERE dim_patient.dss_current_flag = 'Y'
) AS changes
WHERE dim_patient.id = changes.id
AND dim_patient.dss_current_flag = 'Y'
----------------------------------------------------------------
--============================================================================
-- Insert new records
--============================================================================
INSERT INTO [dim].[patient] WITH ( TABLOCK )
( id
, client
, DOB
, Gender_Identity_code
, StateID_number
, dss_load_date
, dss_start_date
, dss_end_date
, dss_current_flag
, dss_version
, dss_create_time
, dss_update_time
)
SELECT DISTINCT
stage_patient.id
, stage_patient.client
, stage_patient.DOB
, stage_patient.Gender_Identity_code
, stage_patient.StateID_number
, stage_patient.dss_load_date
, CASE WHEN vers.patid IS NULL
THEN CAST('01-JAN-1900' AS datetime)
ELSE @v_current_date
END
, CAST('31-DEC-2999' AS datetime)
, 'Y'
, CASE WHEN vers.patid IS NULL
THEN 1
ELSE vers.dss_version + 1
END
, @v_current_datetime
, @v_current_datetime
FROM [stage].[stage_patient] stage_patient
LEFT OUTER JOIN (
SELECT patid
, MAX(dss_version) dss_version
FROM [stage].[patient] patient
GROUP BY id
) AS vers
ON stage_patient.id = vers.id
EXCEPT
SELECT patient.id id
, patient.client client
, patient.DOB DOB
, patient.StateID_number StateID_number
, source.dss_load_date dss_load_date
, @v_current_date
, CAST('31-DEC-2999' AS datetime)
, 'Y'
, dss_version + 1
, @v_current_datetime
, @v_current_datetime
FROM [dim].[patient] patient
JOIN
(
SELECT stage_patient.id AS id
, stage_patient.dss_load_date AS dss_load_date
FROM [stage].[stage_patient] stage_patient
) AS source
ON patient.id = source.id
WHERE patient.dss_current_flag = 'Y'
Currently the code is inserting a second row with null for state_id which I dont want.
