I need to build a workflow where it reads new data from an excel file and populates that data to a database table called SCD. Every time there is a new excel file the workflow compares it with the existing data in SCD table. The Database Table has 4 extra columns. Effective Start Date, Effective End Date, Date Created, Date Modified.
I set Date Created and Date Modified to DateTimeNow().
Note: Effective Start/End dates need to be Datetime values. Round to nearest minute. Multiple updates may occur during the day, all changes should be captured).
- SCD table: update Effective End Date on changed records
- SCD table: append records with changes with Effective Start Date = Effective End Date + 1 minute [Specially need help with this part as both effective start and end date is in DateTime, my sql isn't working in the formula tool.]
Can anyone help with this workflow? I replicated the input data and SCD table as an attachment.
