SQL Server - How to Use Merge Statement for Slowly Changing Dimension with More Than Two Conditions?

Question

I am trying to implement Slowly Changing Dimension Type 2 through T-SQL but I can't figure out how to put a request to work.

Table columns: cpf, nome, telefone_update, endereco_insert

Basically the logic is: if the MATCH doesn't happen using cpf, then the record must be inserted; if the MATCH happens but only the telefone_update field has changed, there is no need to another record and I just want to UPDATE and override the values; if the MATCH happens but only the endereco_insert field has changed I want to add a new record and update the start and end dates.

What I have so far is:

insert into #dm_lucas_tst   (   
                                [cpf],
                                [nome],
                                [telefone_update],
                                [endereco_insert],
                                [dt_scd_start],
                                [dt_scd_end],
                                [nu_scd_version]
                            )
select  [cpf],
        [nome],
        [telefone_update],
        [endereco_insert],
        cast(dateadd(month, datediff(month, 0, getdate()), 0) as date) as [dt_scd_start],
        '2199-12-31' AS [dt_scd_end],
        1 AS [nu_scd_version]
from    (
            merge  edw.dim.dm_lucas_tst as Target
            using  edw.dim.stg_lucas_tst as Source
                on Target.cpf = Source.cpf
            when not matched by target
            then
                insert (
                        [cpf],
                        [nome],
                        [telefone_update],
                        [endereco_insert],
                        [dt_scd_start],
                        [dt_scd_end],
                        [nu_scd_version]
                        )
                values (
                        Source.[cpf],
                        Source.[nome],
                        Source.[telefone_update],
                        Source.[endereco_insert],
                        cast(dateadd(month, datediff(month, 0, getdate()), 0) as date),
                        '2199-12-31',
                        1
                        )
            when matched
            and Source.telefone_update <> Target.telefone_update
            and Target.dt_scd_end = '2199-12-31'
            then
                update set telefone_update = Source.telefone_update
            output $ACTION ActionOut,
                        Source.[cpf],
                        Source.[nome],
                        Source.[telefone_update],
                        Source.[endereco_insert]                
        ) AS MergeOut
where   MergeOut.ActionOut = 'UPDATE';

But I don't think putting another WHEN MATCH AND ... will make that work.

Any suggestions?

Thanks in advance!

Answer 1

Accordingly to your description, I'm assuming that you need:

• SCD Type 1 for column [telefone_update]
• SCD Type 2 for column [endereco_insert]

I've used application SCD Merge Wizard to easily create described logic. When I made tests for it - everything looks as expected, I guess. I described the process on my blog - please have a look and tell me if that was exactly what you wanted?

https://sqlplayer.net/2018/01/scd-type-1-type-2-in-merge-statement/