Dependency between row figures in SQL-CodePudding

For each category (column Name), there is a dependency between sorted rows (by column dt) such as End_am on row i = Start_am on row i 1.

CREATE TABLE table_name (Name,dt,Start_am,End_am) AS
  SELECT 'A', DATE '2000-01-04', FLOAT 0, FLOAT 20 FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-05', FLOAT 20, FLOAT 0 FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-08', FLOAT 0, FLOAT 15 FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-10', FLOAT 15, FLOAT 25 FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-11', FLOAT 333, FLOAT 25 FROM DUAL UNION ALL
  SELECT 'A', DATE '2000-01-12', FLOAT 25, FLOAT 25 FROM DUAL UNION ALL  
  SELECT 'B', DATE '2001-02-05', FLOAT 1, FLOAT 2 FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-09', FLOAT 2, FLOAT 2 FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-10', FLOAT 2, FLOAT 0 FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-11', FLOAT 0, FLOAT 0 FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-12', FLOAT 0, FLOAT -1 FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-13', FLOAT -1, FLOAT 0 FROM DUAL UNION ALL
  SELECT 'B', DATE '2001-02-14', FLOAT 0, FLOAT 0 FROM DUAL;

In example above, data with name B is consistent whereas name A has a mismatch on 2000-01-11 (starting amount 333 vs. previous day ending amount 25).

Is it possible to check in sql?

CodePudding user response：

We can use the LAG() analytic function here:

WITH cte AS (
    SELECT t.*, LAG(End_am, 1, Start_am) OVER (PARTITION BY Name ORDER BY dt) AS End_am_lag
    FROM table_name t
)

SELECT Name, dt, Start_am, End_am
FROM cte
WHERE Start_am <> End_am_lag;