Return a column that displays the last known value (MAX DateTime) from a record table for each row by ID - SQL

I know similar questions have been asked before and I'll include some links at the bottom, but can't work it out for my query.

I want to calculate 2 columns in a view that return the last known status (status A and status B) of a person for each row (status change by DateTime).

I have joined two record tables on date_time that contain information about a persons change in status over time. I want to know the latest value for status A for a person when status B changes and visa versa. For example, when a person's status changes from Hungry to Sad what was their latest (or current) StatusB (Active?).

My current query returns a table:

DateTime	ID	StatusA_Old	StatusA_New	StatusB_Old	StatusB_New
2021-02-01 23:57:20.000	1001	NULL	Hungry	NULL	Active
2021-02-02 23:57:20.000	1002	NULL	Sad	NULL	Active
2021-02-03 23:57:20.000	1001	Hungry	Happy	NULL	NULL
2021-02-04 23:57:20.000	1002	NULL	NULL	Active	Inactive

I want to return a table:

DateTime	ID	StatusA_Old	StatusA_New	StatusA_Current	StatusB_Old	StatusB_New	StatusB_Current
2021-02-01 23:57:20.000	1001	NULL	Hungry	Hungry	NULL	Active	Active
2021-02-02 23:57:20.000	1002	NULL	Sad	Sad	NULL	Active	Active
2021-02-03 23:57:20.000	1001	Hungry	Happy	Happy	NULL	NULL	Active
2021-02-04 23:57:20.000	1002	NULL	NULL	Sad	Active	Inactive	Inactive

What have I tried?

I've had about 7 attempts at using subqueries and/or joining the table back on it's self to return the value of StatusA_New/StatusB_New at the MAX(DateTime) of the two original tables.

The closest I've go is probably this:

WITH T AS   (      SELECT           A.DateTime, A.ID, A.StatusA_New      FROM A  )  SELECT       A.DateTime, A.ID, A.StatusA_Old,       A.StatusB_New, T.StatusA_New AS StatusA_Current  FROM       A  LEFT JOIN       T ON (A.DateTime <= T.DateTime) AND (A.ID = T.ID)  

Keeping in mind that my current query does not even look at Status changes in B and does not yet deal with the joined DateTime table shown in the example tables.

The joined Table AB uses the following query:

/*example tables*/  create table A  (      A_DateTime datetime,      ID varchar(10),      StatusA_Old varchar(10),      StatusA_New varchar(10)  )    create table B  (      B_DateTime datetime,      ID varchar(10),      StatusB_Old varchar(10),      StatusB_New varchar(10)  )    insert into A values  ('2021-02-01 23:57:20.000', 1001, NULL, 'Hungry'),  ('2021-02-02 23:57:20.000', 1002, NULL, 'Sad'),  ('2021-02-03 23:57:20.000', 1001, 'Hungry', 'Happy')    insert into B values  ('2021-02-01 23:57:20.000', 1001, NULL, 'Active'),  ('2021-02-02 23:57:20.000', 1002, NULL, 'Active'),  ('2021-02-04 23:57:20.000', 1002, 'Active', 'Inactive')    SELECT         CASE           WHEN A.A_DateTime IS NULL THEN B.B_DateTime          ELSE A.A_DateTime       END AS DateTime,       CASE           WHEN A.ID IS NULL THEN B.ID          ELSE A.ID       END AS ID,       StatusA_Old, StatusA_New,        StatusB_Old, StatusB_New  FROM        A  FULL OUTER JOIN       B ON A.A_DateTime = B.B_DateTime  

Similar questions:

• Retrieve last known value for each column of a row
• SQL: Select the last record for each day given datetime
• Retrieving the last record in each group - MySQL
• SQL: How to fill empty cells with previous row value?
• Also might relate to this problem (as mentioned by another user) https://www.itprotoday.com/sql-server/last-non-null-puzzle

https://stackoverflow.com/questions/67428420/return-a-column-that-displays-the-last-known-value-max-datetime-from-a-record May 07, 2021 at 10:52AM