create_a_historical_data_archive
Differences
This shows you the differences between two versions of the page.
| — |
create_a_historical_data_archive [2016/03/02 20:49] (current) markmeteenk created |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| + | <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> | ||
create_a_historical_data_archive.txt ยท Last modified: 2016/03/02 20:49 by markmeteenk
Page Tools
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
