I have a current table:
| ID | Name | Salary | Status |
|---|---|---|---|
| 1 | John | 50,000 | Active |
| 2 | Joe | 55,000 | Active |
| 3 | Jake | 40,000 | Inactive |
| 4 | James | 80,000 | Active |
This table is created using a truncate reload query. However I want to now record historical data using a type 2 table. I know I need to add a start and end date field to indicate whether a row is the current or historical data for an employee.
Lets say the next day the source table looks like such:
| ID | Name | Salary | Status |
|---|---|---|---|
| 1 | John | 55,000 | Active |
| 2 | Joe | 55,000 | Inactive |
| 3 | Jake | 40,000 | Inactive |
| 4 | James | 80,000 | Active |
Where John got a increase Salary, and Joe is now inactive.
I want my Type 2 table to now look like:
| ID | Name | Salary | Status | start date | end date | active flag |
|---|---|---|---|---|---|---|
| 1 | John | 55,000 | Active | 10-26-2022 | null | Y |
| 1 | John | 50,000 | Active | 10-25-2022 | 10-26-2022 | N |
| 2 | Joe | 55,000 | Active | 10-26-2022 | null | Y |
| 2 | Joe | 55,000 | Inactive | 10-25-2022 | 10-26-2022 | N |
| 3 | Jake | 40,000 | Inactive | 10-26-2022 | null | Y |
| 4 | James | 80,000 | Active | 10-26-2022 | null | Y |
I am not familiar with creating a merge query to create this sort of performance, how can I go about doing so?