{"id":294,"date":"2025-09-05T01:07:15","date_gmt":"2025-09-05T01:07:15","guid":{"rendered":"https:\/\/supportingelement.com\/?p=294"},"modified":"2025-09-05T01:07:15","modified_gmt":"2025-09-05T01:07:15","slug":"identifying-field-changes-using-the-lag-function","status":"publish","type":"post","link":"https:\/\/supportingelement.com\/index.php\/2025\/09\/05\/identifying-field-changes-using-the-lag-function\/","title":{"rendered":"Identifying Field Changes using the “LAG” Function"},"content":{"rendered":"\n

There are a number of scenarios where it is necessary to identify changes in specific fields in Effective Dated data sets. For example: identifying changes to Full\/Part Time Status in Job Data.<\/p>\n\n\n\n

The following screenshot shows the results of a PS Query that uses the Oracle SQL “LAG” function to identify changes to the FULL_PART_TIME field in Job Data. The Full\/Part Time Status for the selected employee was changed from “P” to “F” on 19th Jul 2025.<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

The Oracle SQL “Lag” function is used to create the “Prev Full\/Part” field shown above. This function retrieves the field value from the previous row in the data set, as follows:<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n
LAG(A.FULL_PART_TIME, 1, 0)
OVER (ORDER BY <\/code>
A.EMPLID, <\/code>
A.EMPL_RCD,
A.EFFDT,
A.EFFSEQ)<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

The “Expression” can then be used a a field in the PS Query, as shown below:<\/p>\n\n\n

\n
\"\"<\/figure>\n<\/div>\n\n\n

Microsoft SQL Server also has a “LAG” function, but with slightly different syntax.<\/p>\n","protected":false},"excerpt":{"rendered":"

There are a number of scenarios where it is necessary to identify changes in specific fields in Effective Dated data sets. For example: identifying changes to Full\/Part Time Status in Job Data. The following screenshot shows the results of a PS Query that uses the Oracle SQL “LAG” function to identify changes to the FULL_PART_TIME\u2026 Read More »<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-294","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts\/294","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/comments?post=294"}],"version-history":[{"count":1,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts\/294\/revisions"}],"predecessor-version":[{"id":298,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/posts\/294\/revisions\/298"}],"wp:attachment":[{"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/media?parent=294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/categories?post=294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/supportingelement.com\/index.php\/wp-json\/wp\/v2\/tags?post=294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}