update master table

[Tal]

hi,

I have a master table which gets  updated  by daily SAS datasets.

a) if the record  from the  daily  table is new  then simply append it to the master table

b) if the record exists(no changes)  in the master then do nothing to it

c)if the clientt exists in the master but he has some  updates in the  daily  file then append  his new reocrd to the master and also update the Date field of his previous record with the current date.

In other words after the appending only clients  whose two last records in the master table    have missing date need to be updated(the Date field  of his second last record)

I have attached samles  of how  table need to look like

Tried  with last.id and   first.id but  got stuck. Any hints  please?

Thx

[Patrick · Accepted solution]

@Tal

Looks like a slowly changing dimension type 2 (SCD2) to me. If so then I'd have the following columns in the table:

valid_from_dt   (that's your current date column)

valid_to_dt       (set to something far in the future if current record, else date when expired and new current record added)

change_current_ind   (1 if current record, else 0)

Primary key of table: {ID, valid_from_dt}

If you've got SAS DI Studio then use transformation SCD Type 2. This one does the job for you.

Without SAS DI Studio:

1. SQL to determine in your daily increment table which ID's are brand new (inserts) and which one are a change (Update, Insert)

2. Update of all records in master where ID with a change (= valid_to_dt set to date of your increment table, change_current_ind set to 0)

3. Insert of all new and changed record from increment table

What about deletes? Does that ever happen? If so then you'd need another delta file with the deletes (just ID's) used to expire records in your master (= update of valid_to_dt)

If your transaction data set got full volumes then a delete would be all ID's which exist in Master but don't exist in transaction DS.

[Tal]

Thx Patrick
I can get the records (A) from the daily file which are brand new and those with changes. So are you saying i can left join the master with (A) and flag it with "new" and "existing" and if its existing than set the date to current and then simply append (A) to the master?

[Patrick]

@Tal

So are you saying i can left join the master with (A) and flag it with "new" and "existing" and if its existing than set the date to current and then simply append (A) to the master

From what you've wrote initially you'll also have records in (A) which are identical with master and for these you can't just update the date in master and you need also to exclude these for Insert.

(A) left join Master over ID where Master.Change_Current_Ind=1

- No match: New Record

- Match: 

   - if difference in one of the other columns under change tracking then Update Record

   - if all columns under change tracking identical then "throw away".

- Update Master with (A) where Master.ID=A.ID and Master.Change_Current_Ind=1 and A.<record_type>=<update>

      set valid_to_dt to current date (data date of table A)

      set change_current_ind=0

- Append (Insert) all records from (A) where A.<record_type> in (<new>,<update>)

      valid_from_dt=<data date of table A>

      valid_to_dt=<date in far future>

      change_current_ind=1

Does that make sense?

[Tal]

In fact flag the master with "existing" only and update the date to current and then append (A) to the master table?