Wiki MaCa

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<code>
use test
go

select * from [psygis].[zorgc_stg]
order by zorgc_iden, audit_id

truncate table test.psygis.zorgc_hda

insert into test.psygis.zorgc_hda
select [audit_id],'2016-03-01 11:43:29.890','9999-12-31',[voided],[mut_dat],[mut_tyd],[mut_gbr],[zorgc_iden],[act],[oms],[zgs],[dbc]
from [psygis].[zorgc_stg] stg
where stg.audit_id = 2251

UPDATE hda
SET voided = CASE WHEN stg.audit_id IS NOT NULL THEN 0 ELSE 1 END
, load_date_end = '2016-03-02 11:43:29.890'
FROM  test.psygis.zorgc_hda hda
LEFT OUTER JOIN test.psygis.zorgc_stg stg
  ON 1=1 AND stg.audit_id = 2852 AND stg.stg_sequence = 1  
  AND ( hda.[zorgc_iden] = stg.[zorgc_iden] OR (hda.[zorgc_iden] IS NULL AND stg.[zorgc_iden] IS NULL))
WHERE hda.voided = 0 AND hda.load_date_end = '9999-12-31'
AND (( hda.[mut_dat] <> stg.[mut_dat] OR ((hda.[mut_dat] IS NULL AND stg.[mut_dat] IS NOT NULL) OR (hda.[mut_dat] IS NOT NULL AND stg.[mut_dat] IS NULL)))
OR ( hda.[mut_tyd] <> stg.[mut_tyd] OR ((hda.[mut_tyd] IS NULL AND stg.[mut_tyd] IS NOT NULL) OR (hda.[mut_tyd] IS NOT NULL AND stg.[mut_tyd] IS NULL)))
OR ( hda.[mut_gbr] <> stg.[mut_gbr] OR ((hda.[mut_gbr] IS NULL AND stg.[mut_gbr] IS NOT NULL) OR (hda.[mut_gbr] IS NOT NULL AND stg.[mut_gbr] IS NULL)))
OR ( hda.[act] <> stg.[act] OR ((hda.[act] IS NULL AND stg.[act] IS NOT NULL) OR (hda.[mut_gbr] IS NOT NULL AND stg.[mut_gbr] IS NULL)))
OR ( hda.[oms] <> stg.[oms] OR ((hda.[oms] IS NULL AND stg.[oms] IS NOT NULL) OR (hda.[oms] IS NOT NULL AND stg.[oms] IS NULL)))
OR ( hda.[zgs] <> stg.[zgs] OR ((hda.[zgs] IS NULL AND stg.[zgs] IS NOT NULL) OR (hda.[zgs] IS NOT NULL AND stg.[zgs] IS NULL)))
OR ( hda.[dbc] <> stg.[dbc] OR ((hda.[dbc] IS NULL AND stg.[dbc] IS NOT NULL) OR (hda.[dbc] IS NOT NULL AND stg.[dbc] IS NULL))))
OR stg.audit_id IS NULL

insert into test.psygis.zorgc_hda
select 
2852,'2016-03-02 11:43:29.890','9999-12-31',stg.[voided],stg.[mut_dat],stg.[mut_tyd],stg.[mut_gbr],stg.[zorgc_iden],stg.[act],stg.[oms],stg.[zgs],stg.[dbc]
--, case when hda.audit_id IS NULL then 'nieuw' else 'gewijzigd' end , case when hda.load_date_end = '2016-03-01 11:43:29.890' then 'gewijzigd' else 'nieuw' end 
from test.psygis.zorgc_stg stg
LEFT OUTER JOIN test.psygis.zorgc_hda hda
  ON 1=1 
  AND  hda.voided = 0 
  AND ( hda.[zorgc_iden] = stg.[zorgc_iden] OR (hda.[zorgc_iden] IS NULL AND stg.[zorgc_iden] IS NULL))
WHERE stg.audit_id = 2852 AND stg.stg_sequence = 1 
AND (
  hda.load_date_end = '2016-03-02 11:43:29.890' -- gewijzigde
  OR
  hda.audit_id IS NULL -- nieuwe
)

select * from test.psygis.zorgc_hda
order by zorgc_iden, load_date
</code>
Wiki MaCa

User Tools

Site Tools

Page Tools
