Combining and Tracking Unique Changes Across Two Tables in SQLite

Issue Overview: Combining and Tracking Unique Changes Across Two Tables

The core issue revolves around combining data from two tables, Project_List and ABT_Budget, in SQLite to produce a unified view that tracks unique changes in specific columns over time. The goal is to create a result set that lists non-repeated combinations of FinState, CAPEX, BL_Cost, tEAC, and Dossier columns, while ensuring that the data is aligned by date and that any missing values are filled with the last known value or a default (e.g., 0).

The challenge lies in the fact that the two tables have different schemas and update frequencies. The Project_List table contains project-related data such as ProjID, InsertDate, Financial_Status, Actual_Capex, and Baseline_Cost. The ABT_Budget table, on the other hand, contains budget-related data such as ProjID, InsertDate, EAC, and AnnualDossier. The desired output should reflect the state of all relevant columns at each point in time, even if some columns did not change.

For example, if the Financial_Status changes from Hold to Closed, the output should reflect this change along with the corresponding values of CAPEX, BL_Cost, tEAC, and Dossier at that time. If the tEAC or Dossier values are not available for a specific date, the last known values should be carried forward.

Possible Causes: Misalignment of Data and Lack of Proper Joins

The primary cause of the issue is the misalignment of data between the two tables. The Project_List and ABT_Budget tables are updated independently, and their InsertDate values do not always match. This misalignment makes it difficult to directly join the tables and produce a coherent result set.

Another contributing factor is the lack of a proper mechanism to carry forward the last known values for columns that do not change frequently. For example, if the tEAC value is updated in the ABT_Budget table but the Financial_Status in the Project_List table remains unchanged, the query should still reflect the new tEAC value while retaining the existing Financial_Status.

Additionally, the initial attempts to solve the problem involved using an INNER JOIN between the two tables, which only returns rows where there is a match in both tables. This approach fails to account for rows where data might be present in one table but not the other, leading to incomplete results.

Troubleshooting Steps, Solutions & Fixes: Leveraging Window Functions and Coalesce

To address the issue, we need to employ a combination of techniques, including window functions, subqueries, and the COALESCE function. The solution involves several steps:

1. Create a Unified Table: First, we create a new table (t3) that combines the relevant columns from both Project_List and ABT_Budget. This table will serve as a staging area for further processing.

   CREATE TABLE t3 (
       projid TEXT,
       adate TEXT,
       finstate TEXT,
       capex TEXT,
       bl_cost TEXT,
       bdate TEXT,
       teac TEXT,
       dossier TEXT
   );
   

2. Populate the Unified Table: Insert data from the Project_List table into t3, leaving the bdate, teac, and dossier columns as NULL for now.

   INSERT INTO t3 (projid, adate, finstate, capex, bl_cost, bdate, teac, dossier)
   SELECT projid, adate, finstate, capex, bl_cost, NULL, NULL, NULL
   FROM Project_List;
   

3. Update the Unified Table with Budget Data: Use a subquery to update the t3 table with the latest bdate, teac, and dossier values from the ABT_Budget table. This step ensures that each row in t3 has the most recent budget data up to the corresponding adate.

   UPDATE t3
   SET (bdate, teac, dossier) = (
       SELECT bdate, teac, dossier
       FROM ABT_Budget
       WHERE projid = t3.projid
         AND bdate <= t3.adate
       ORDER BY bdate DESC
       LIMIT 1
   );
   

4. Identify Unique Changes Using Window Functions: Use window functions to track changes in the finstate, capex, bl_cost, teac, and dossier columns. The LAG function is particularly useful for comparing the current row with the previous row to detect changes.

   WITH t4 AS (
       SELECT projid,
              adate,
              finstate,
              capex,
              bl_cost,
              teac,
              dossier,
              LAG(projid) OVER win1 AS lagprojid,
              LAG(finstate) OVER win1 AS lagfinstate,
              LAG(capex) OVER win1 AS lagcapex,
              LAG(bl_cost) OVER win1 AS lagbl_cost,
              LAG(teac) OVER win1 AS lagteac,
              LAG(dossier) OVER win1 AS lagdossier
       FROM t3
       WINDOW win1 AS (PARTITION BY projid ORDER BY adate)
   )
   SELECT projid,
          adate,
          finstate,
          capex,
          bl_cost,
          teac,
          dossier
   FROM t4
   WHERE NOT (projid IS lagprojid
              AND finstate IS lagfinstate
              AND capex IS lagcapex
              AND bl_cost IS lagbl_cost
              AND teac IS lagteac
              AND dossier IS lagdossier)
   ORDER BY projid, adate;
   

5. Handle NULL Values with COALESCE: In the final result set, use the COALESCE function to replace any NULL values with a default value (e.g., 0). This ensures that the output is consistent and does not contain missing data.

   WITH t4 AS (
       SELECT projid,
              adate,
              finstate,
              capex,
              bl_cost,
              COALESCE(teac, '0') AS teac,
              COALESCE(dossier, '0') AS dossier,
              LAG(projid) OVER win1 AS lagprojid,
              LAG(finstate) OVER win1 AS lagfinstate,
              LAG(capex) OVER win1 AS lagcapex,
              LAG(bl_cost) OVER win1 AS lagbl_cost,
              LAG(teac) OVER win1 AS lagteac,
              LAG(dossier) OVER win1 AS lagdossier
       FROM t3
       WINDOW win1 AS (PARTITION BY projid ORDER BY adate)
   )
   SELECT projid,
          adate,
          finstate,
          capex,
          bl_cost,
          teac,
          dossier
   FROM t4
   WHERE NOT (projid IS lagprojid
              AND finstate IS lagfinstate
              AND capex IS lagcapex
              AND bl_cost IS lagbl_cost
              AND teac IS lagteac
              AND dossier IS lagdossier)
   ORDER BY projid, adate;
   

Conclusion

By following the above steps, we can effectively combine and track unique changes across two tables in SQLite. The key to solving this problem lies in the use of window functions to detect changes and the COALESCE function to handle missing data. This approach ensures that the final result set accurately reflects the state of the project at each point in time, even when data is updated independently in the two tables.

This solution is not only robust but also scalable, as it can be adapted to handle additional columns or more complex scenarios. By leveraging SQLite’s powerful features, we can achieve a high level of data integration and consistency, making it easier to analyze and report on project and budget data.