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 field in Job Data. The Full/Part Time Status for the selected employee was changed from “P” to “F” on 19th Jul 2025.
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:
LAG(A.FULL_PART_TIME, 1, 0) A.EMPLID, A.EMPL_RCD, |
The “Expression” can then be used a a field in the PS Query, as shown below:
Microsoft SQL Server also has a “LAG” function, but with slightly different syntax.