Finding instances where the earliest date/time value in Table A does not match a "Created" date/time from another table

user_19898311 0
Viewed 29

I could use some help with a query to help me identify data missing from a table. In my example, I have a history table missing the initial entry, usually created by a trigger from another table, but the trigger was turned off. This table - we will call Table B - contains a DateTimeChanged and a DateTimeCreated column. The DateTimeChanged and DateTimeCreated columns should be equal on the initial entry to the table. However, since this trigger was off, I am missing an entry for a specific period.

I aim to identify instances where the earliest DateTimeChanged for each group of records does not match the DateTimeCreated value. The earliest DateTimeChanged for each record should always be equal to the DateTimeCreated. I can better determine my next steps by identifying where this is not true for my current data set.

Here is an example of the data:

Table A (Live)

ID Company Created Changed
123456 ABC123 2021-11-10 14:09:44.920 2022-05-19 11:13:17.137

Table B (History)

ID Company Created Changed
123456 ABC123 2021-11-10 14:09:44.920 2022-05-06 10:08:09.263
123456 ABC123 2021-11-10 14:09:44.920 2022-05-19 11:13:17.137

In Table B, there is a missing row where the Created and Changed date should be the same. This is because a new line should be created in Table B upon any insert, update, or deletion from Table A, but the trigger was disabled.

How can I find all instances where the earliest record in Table B does not have a row with a DateTimeChanged equal to the DateTimeCreated in Table A for my ID and Company combination? I believe I would have to use the min(DateTimeChanged) in Table B to compare against the DateTimeCreated in Table A. But I have yet to find an effective way to do this for each ID and Company combination I have.

Thank you for your help!

sql-server sql
1 Answers

To INSERT missing rows from Table A into Table B:

Sync B with A - Add Existent A Rows to B

You can perform a JOIN from Table A to Table B, then GROUP BY your columns HAVING MAX Changed Date in Table A > MAX Changed Date in Table B.

To SELECT:

   SELECT A.ID, A.Company, A.Created, A.Changed 
   FROM TableA A
   INNER JOIN TableB B
   ON A.ID = B.ID AND A.Company = B.Company
   GROUP BY A.ID, A.Company, A.Created, A.Changed 
   HAVING MAX(A.Changed) > MAX(B.Changed)

To INSERT:

INSERT INTO TableB (ID, Company, Created, Changed)
SELECT A.ID, A.Company, A.Created, A.Changed 
FROM TableA A
INNER JOIN TableB B
ON A.ID = B.ID AND A.Company = B.Company
GROUP BY A.ID, A.Company, A.Created, A.Changed 
HAVING MAX(A.Changed) > MAX(B.Changed)

See Fiddle.

On the flip side:

To DELETE unmatched rows from Table A in Table B:

Sync A with B - Removing Non-Existent A Rows from B

You can perform a JOIN from Table B to Table A, then GROUP BY your columns HAVING MIN Changed Date in Table B < MIN Changed Date in Table A.

To SELECT:

SELECT B.ID, B.Company, B.Created, B.Changed 
FROM TableB B
INNER JOIN TableA A 
ON A.ID = B.ID AND A.Company = B.Company
GROUP BY B.ID, B.Company, B.Created, B.Changed 
HAVING MIN(B.Changed) < MIN(A.Changed)

To DELETE:

Note, for the DELETE, you need JOIN by all columns to essentially create a key to JOIN on since you don't have a UNIQUE primary key constraint on Table B, being that its a historical table.

DELETE C
  FROM TableB C
JOIN
(
  SELECT B.ID, B.Company, B.Created, B.Changed 
  FROM TableB B
  INNER JOIN TableA A 
  ON A.ID = B.ID AND A.Company = B.Company
  GROUP BY B.ID, B.Company, B.Created, B.Changed 
  HAVING MIN(B.Changed) < MIN(A.Changed) 
) D ON C.ID = D.ID AND C.Company = D.Company 
AND C.Created = D.Created AND C.Changed = D.Change

See Fiddle.

user_30928470
Related