1

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');
1
  • Provide sample data as CREATE TABLE + INSERT INTO.
    – Akina
    Commented Jan 26 at 6:10

1 Answer 1

2

What you need to do is join table2 to it self and filter out the amount in when it is less meaning when it's changed to 0000 and then filter the date to leatest this one specialy for workID 251 cause it have lower date thats why doesn't show correctly when using min in you soulation. now we can group them by min date, after a just join everything toghter and apply the final filter to status and type.

with cte as (
SELECT 
    t1.workID2,
    min(t1.h2Dt) 'dateChanged'
from table2 t1
join table2 t2 
on t1.workID2 = t2.workID2
and t1.h2Amt < t2.h2Amt
and t1.h2Dt > t2.h2Dt
GROUP by t1.workID2
)
SELECT 
    t2.workID2 ,
    t2.h2Amt ,
    t2.h2Dt ,
    t2.type1,
    t1.status 
from table2 t2
join cte ct on 
t2.workID2 = ct.workID2
and t2.h2Dt = ct.dateChanged
join table1 t1 on 
t2.workID2 = t1.workID1 
WHERE 
    t2.type1 in (1,2,3)
    and t1.status = 'A'
1
  • For some reason, this db won't let me use cte's or really any kind of windows functions. I have been trying to use subqueries to find solutions. It's a third party application an db. I think it's mysql.
    – tkmagnet
    Commented Mar 19 at 19:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.