{"id":3831,"date":"2023-09-05T14:32:17","date_gmt":"2023-09-05T14:32:17","guid":{"rendered":"http:\/\/van-maanen.com\/?p=3831"},"modified":"2023-09-05T14:32:17","modified_gmt":"2023-09-05T14:32:17","slug":"closing-records","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=3831","title":{"rendered":"Closing records"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">In a data warehouse environment, we may have to close records when we have new records that are added to the system. Let me first provide the sql.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CASE mut.etl_valid_from<br>WHEN MAX(mut.etl_valid_from) OVER<br>(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr))<br>THEN mut.etl_valid_to<br>ELSE LEAD(mut.etl_valid_from) OVER<br>(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr)<br>ORDER BY mut.etl_valid_from)<br>END<\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">This is a really nice concise text. Let me show a few items. With the CASE clause, the records are selected that have a maximum for the timestamp when records are entered. The dataset is split according to company code and the gl_account_nr. So for each combination of company code and the gl_account_nr, the maximum of etl_validated_from is calculated. When the etl_valid_from is equal to the maximum, a provided etl_valid_to is inserted into the data base. When etl_valid_from has a lower value, the data from a leading record is taken. That value is the etl_valid_from a leading record that stems from the same bracket of company code and the gl_account_nr that is ordered on etl_valid_from.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In a data warehouse environment, we may have to close records when we have new records that are added to the system. Let me first provide the sql. CASE mut.etl_valid_fromWHEN MAX(mut.etl_valid_from) OVER(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr))THEN mut.etl_valid_toELSE LEAD(mut.etl_valid_from) OVER(PARTITION BY TO_NUMBER(mut.company_code||mut.gl_account_nr)ORDER BY mut.etl_valid_from)END This is a really nice concise text. Let me show a few items. With [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[],"class_list":["post-3831","post","type-post","status-publish","format-standard","hentry","category-niet-gecategoriseerd"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3831","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3831"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3831\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3831"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3831"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3831"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}