Query to find when a specific current value changed

I have 3 tables of data (all contain more columns, but these are the ones I need from each): t1: table1 | workID1 | h1Amt | h1Dt | status | |:-------|:-----|:----|:-------| | 101 |73.00 |12/1/2023| A | | 124 |0.00 |10/30/2023| D | | 251 |0.00 |3/1/2023| A | | 255 |24.00 |11/30/2023| A | | 256 |0.00 |12/1/2023| A | t2: table2 | workID2 | h2Amt | h2Dt | type1 | |:-------|:------|:----|:-----| | 101 | 0073 |202312| 2 | | 101 | 0000 |202308| 13 | | 101 | 0000 |202307| 1 | | 124 | 0000 |202310| 3 | | 124 | 0000 |202306| 2 | | 124 | 0000 |202304| 13| | 124 | 0079 |202301| 3 | | 251 | 0000 |202303| 2 | | 251 | 1154 |202301| 3 | | 251 | 0000 |202212| 3 | | 255 | 0024 |202311| 2 | | 255 | 0000 |202212| 3 | | 255 | 0000 |202210| 13 | | 255 | 0175 |202209| 3 | | 256 | 0000 |202312| 1 | | 256 | 0000 |202309| 13| | 256 | 0000 |202307| 3 | | 256 | 0583 |202305| 2 | t3: table3 | workID3 | h3Date | type2 | h3Dt | |:-------|:----|:-----|:-------| | 101 |12/1/2023| 2 | 202312 | | 101 |8/13/2023| 13 | 202308 | | 101 |7/1/2023 | 1 | 202307 | | 124 |10/1/2023| 3 | 202310 | | 124 |6/1/2023| 2 | 202306 | | 124 |4/11/2023| 13| 202304 | | 124 |1/1/2023| 3 | 202301 | | 251 |3/1/2023| 2 | 202303 | | 251 |1/1/2023| 3 | 202301 | | 251 |12/1/2022| 3 | 202212 | | 255 |11/1/2023| 2 | 202311 | | 255 |12/1/20022| 3 | 202212 | | 255 |10/7/2022| 13 | 202210 | | 255 |9/1/2022| 3 | 202209 | | 256 |12/1/2023| 1 | 202312 | | 256 |9/5/2023| 13| 202309 | | 256 |7/1/2023| 3 | 202307 | | 256 |5/1/2023| 2 | 202305 | In t1, there is only one row for each workID, which is the current transaction for that workID. As data gets added, all 3 tables gets updated, with t1 only showing the current transaction. In t2 and t3, there are multiple rows because it shows historical transactions for each workID. The h1Amt and h2Amt columns show the same cost, but formatted differently. Additionally, the h1Dt, h2Dt, and h3Dt all reflect the same date, just formatted differently. I need to find a specific current value and when that value last changed. In my case, I am searching for an amount of 0.00 or 0000 and when it was last changed to this value. Also, the status (t1) has to be "A" and type (t2 or t3) has to be 1, 2, or 3. Here is the output I'm looking for: | workID | amount | date | type | |:-------|:--------|:-----|:-----| | 251 |0.00 or 0000|202303 or 3/1/2023| 2 | | 256 |0.00 or 0000|202307 or 7/1/2023| 3 | This is what I came up with so far, but it is returning amounts that are not currently 0000 (0.00) or not showing workID's that have a current 0000 (0.00). select c.workID3 ,b.currAmt ,b.curStart ,c.type2 from table3 c inner join table1 t on t.workID1 = c.workID3 inner join ( select b.workID2 ,max( case when h1Amt = 0.00 and b.h2Dt = d.h3dt then h1Amt end) as curAmt ,min( case when h1Amt = 0.00 and b.h2Dt = d.h3Dt then d.h3Date end) as curStart from table2 b inner join table3 d on d.workID3 = b.workID2 inner join table1 on workID1 = b.workID2 where not exists ( select 1 from table2 a where a.workID2 = b.workID2 and a.h2Amt b.h2Amt and a.h2Dt > b.h2Dt ) and t.h1Amt = 0.00 and t.status = 'A' and d.type2 in ('1','2','3') group by b.workID2 ) b on b.workID2 = c.workID3 and b.curStart = c.h3Dt order by c.workID3 I haven't used table3, mostly because table2 contains h2Amt that is needed. I don't think it would be smart for me to use h1Amt from table1 since it only shows what is current. At the very least I need to use table1 because of the status column. If I did use table3, table1 would be a requirement since it's the only table with a status column, but I think I would run into the issue of finding the historical amount since it's not recorded in either table1 or table3. Also, I've run into issues of getting duplicate rows in other queries that I've tried, to include using all 3 tables. The above is the closest query I've seen after doing a lot of searching for similar situations. Hopefully I've given enough detail to find a solution. Thanks in advance! Edit: in table3, there are 2 date columns, h3Dt and h3Date. Table3 has been updated. I also updated the query that I currently have. It returns most correctly, but still has some that returns the wrong date. Also, as I was looking at the data, table2 type1 has some data that is incorrect vs table 3 type 2, so I decided to use table 3 as well in the above query as it is more accurate. The only issue left now is how I can get the correct data for all rows. Here is some sample data: create table table1 ( workID char(3), h1Amt char(10), h1Dt varchar(255), status char(1) ); insert into table1 ( workID, h1Amt, h1Dt, status values ('101','73.00','12/1/2023','A'), ('124','0.00','10/30/2023','D'), ('251','0.00','3/1/2023','A'), ('255','24.00','11/30/2023','A'), ('256','0.00','12/1/2023','A'); create table table2 ( workID2 char(3), h2Amt char(4), h2Dt char(4), type1 char(2) ); insert into table2 ( workID2, h2Amt, h2Dt, type1 values ('101','0073','202312','2'), ('101','0000','202308','13'), ('101','0000','202307','1'), ('124','0000','202310','3'), ('124','0000','202306','2'), ('124','0000','202304','13'), ('124','0079','202301','3'), ('251','0000','202303','2'), ('251','1154','202301','2'), ('251','0000','202212','3'), ('255','0024','202311','2'), ('255','0000','202212','3'), ('255','0000','202210','13'), ('255','0175','202209','3'), ('256','0000','202312','1'), ('256','0000','202309','13'), ('256','0000','202307','3'), ('256','0583','202305','2'); create table table3 ( workID3 char(3), h3Date varchar(10), type2 char(2), h3Dt char(4) ); insert into table3 ( workID3, h3Date, type2, h3Dt values ('101','12/1/2023','2','202312'), ('101','8/13/2023','13','202308'), ('101','7/1/2023','1','202307'), ('124','10/1/2023','3','202310'), ('124','6/1/2023','2','202306'), ('124','4/11/2023','13','202304'), ('124','1/1/2023','3','202301'), ('251','3/1/2023','2','202303'), ('251','1/1/2023','3','202301'), ('251','12/1/2022','3','202212'), ('255','11/1/2023','2','202311'), ('255','12/1/2022','3','202212'), ('255','10/7/2022','13','202210'), ('255','9/1/2022','3','202209'), ('256','12/1/2023','1','202312'), ('256','9/5/2023','13','202309'), ('256','7/1/2023','3','202307'), ('256','5/1/2023','2','202305');