0

Trying to query the date that a field changed to a zero amount. Here is some of the data from the tables involved:

-- mbMaster is the master table for all 'm' type tables (mvTable, mcTable), but does not have certain data that the 'm' tables have. The 'm' tables are broken up by program. All of the 'm' tables contain past to current data.

create table mbMaster (
    mbID char(5),
    mbShortDate char(10),
    mbAction char(2),
    mbEffectiveDate varchar (12) );

insert into mbMaster (
    mbID, mbShortDate, mbAction, mbEffectiveDate)
values('11984','202405','3','5/1/2021'),
    ('11984','202403','2','3/1/2024'),
    ('11984','202312','13','12/13/2023'),
    ('11984','202303','2','3/1/2023'),
    ('11984','202212','13','12/13/2022'),
    ('12385','202402','2','2/29/2024'),
    ('12385','202401','10','2/31/2024'),
    ('12385','202311','2','11/1/2023'),
    ('12385','202310','13','10/9/2023'),
    ('12385','202308','3','8/1/2023'),
    ('12385','202306','3','6/1/2023'),
    ('17976','202403','3','3/1/2024'),
    ('17976','202401','10','1/31/2024'),
    ('17976','202310','13','10/23/2023'),
    ('17976','202310','2','10/1/2023'),
    ('17976','202303','3','3/1/2023'),
    ('17976','202210','1','10/18/2022'),
    ('18489','202404','3','4/1/2024'),
    ('18489','202310','3','10/1/2023'),
    ('18489','202307','1','7/1/2023'),
    ('12845','202402','3','2/1/2024'),
    ('12845','202311','2','11/1/2023'),
    ('12845','202310','13','10/16/2023'),
    ('12845','202305','3','5/1/2023'),
    ('12845','202304','3','4/1/2023'),
    ('19976','202311','3','11/1/2023'),
    ('19976','202306','2','6/1/2023'),
    ('19976','202305','13','5/2/2023'),
    ('19976','202210','3','10/1/2022');

-- tenMain only shows current information
create table tenMain (
    tenID char(5),
    tenPayment varchar(10),
    tenStatus char(1) );

insert into tenMain (
    tenID, tenPayment, tenStatus)
values('11984','46.00','A'),
    ('12385','0.00','A'),
    ('17976','0.00','A'),
    ('18489','136.00','A'),
    ('12845','0.00','A'),
    ('19976','0.00','A');

-- c program data
create table mcTable (
    mcID char(5),
    mcShortDate char(6),
    mcPayment char(4) );

insert into mcTable (
    mcID, mcShortDate, mcPayment)
values('17976','202403','0000'),
    ('17976','202401','0000'),
    ('17976','202401','0000'),
    ('17976','202310','0000'),
    ('17976','202310','0000'),
    ('17976','202303','0249'),
    ('17976','202210','0249');

-- v program data
create table mvTable (
    mvID char(5),
    mvShortDate char(6),
    mvPayment char(4) );

insert into mvTable (
    mvID, mvShortDate, mvPayment)
values('11984','202405','0046'),
    ('11984','202403','0000'),
    ('11984','202312','0000'),
    ('11984','202303','0025'),
    ('11984','202212','0000'),
    ('12385','202402','0000'),
    ('12385','202401','0000'),
    ('12385','202311','0000'),
    ('12385','202310','0000'),
    ('12385','202308','0138'),
    ('12385','202306','0138'),
    ('17976','202210','0249',
    ('18489','202404','0136'),
    ('18489','202310','0000'),
    ('18489','202307','0917'),
    ('12845','202402','0000'),
    ('12845','202311','0000'),
    ('12845','202310','0000'),
    ('12845','202305','0357'),
    ('12845','202304','1090'),
    ('19976','202311','0000'),
    ('19976','202306','0093'),
    ('19976','202305','0000'),
    ('19976','202210','0215');

I have a query that includes 3 tables, but is missing data from a fourth similar table (mcTable).

This is where I'm at with the query:

select m2.mbID as member_id
    ,t.startDate as start_date
    ,c.tenPayment as member_payment
from mbMaster m2
inner join tenMain c on c.tenID = m2.mbID
inner join
    (
        select b.mvID
            ,min(
                case
                    when tenPayment = 0.00
                        and b.mvShortDate = m1.mbShortDate
                        and b.mvPayment = '0000' then m1.mbEffectiveDate end
            ) as startDate
        from mvTable b
        inner join tenMain on tenID = b.mvID
        inner join mbMaster m1 on m1.mbID = b.mvID
        where tenStatus = 'A'
            and tenPayment = 0.00
            and m1.mbAction in ('1','2','3')
            and not exists
                (
                    select 1
                    from mvTable a
                    where a.mvID = b.mvID
                        and a.mvPayment <> b.mvPayment
                        and a.mvShortDate > b.mvShortDate
                )
        group by b.mvID
    ) t on t.mvID = m2.mbID
        and t.startDate = m2.mbEffectiveDate
order by m2.mbID

I tried to add mcTable into the max(case) expression, similar to how the mvTable is written, but the query returned nothing. Here's what the result should be:

member_id start_date member_payment
12385 11/1/2023 0.00
12845 11/1/2023 0.00
19976 11/1/2023 0.00

The mcID 17976 should be part of the results once the table is part of the query. Do I need to do another join/self join for the mcTable? This third party app and db uses mysql as far as I can tell. Also, I am very limited as far as what I can use for doing a query. I've noticed that I can't use most of windows functions, so I'm just looking for how I can solve with subqueries. Thanks.

Edit: Got the tables/columns renamed so things make sense (hopefully). The query returns the results needed for finding when the Payments changed to 0.00 or 0000, except for mcTable, which is not part of the query. The mcTable follows the same filter as the mvTable, but I don't know how to include the mcTable that will return mcID 17976 and corresponding mbEffectiveDate, which would be 10/1/2023. Any attempt at adding the mcTable has resulted in no results. The full historical payment amounts for mcID 17976 is only found in mcTable. Hope this all makes sense. Just looking for a solution to add mcIDs with its corresponding mbEffectiveDate to the query.

Improve this question
4
  • besdies that your tabkes have some minor problems, nobdy can undestand the logic behind this. spme foreign keys would at least hellp to understand how the tabkes ae related but even then, why these 4 out of 8
    – nbk
    Commented Mar 26 at 19:29
  • I'm trying to get ID's that currently have either a 0.00 or 0000 depending on the table being used. All the m tables have data that are similar to each other (ex: ID's, shDts,). I have to use mbDt because there is a calculation for 6 months ahead that needs to be added to the query, which I already have. There are other fields that are part of the query, but was taken out because they don't have an effect on the query. The mainTen table is used because it contains the latest amount, so the 2 that have an amount would be filtered out, then all I would need is to find the date the value changed.
    – tkmagnet
    Commented Mar 27 at 13:22
  • please expalin your logic in the question, i fxed your errors and run your code but the inner select yields no result, it is empty with your data, and i would suggest your you read up on naming conventions i sql
    – nbk
    Commented Mar 27 at 14:41
  • The mbMaster table is the master table for all the 'm' type tables. Each 'm' type table goes to a different program, but have the same data in each. The tenMain table just shows the current info. The question is how to add mcTable to the query so that any ID in that table that has a 0.00 current payment will return the date that the payment column changed to zero.
    – tkmagnet
    Commented Mar 27 at 16:14

1 Answer 1

Reset to default
0

Figured out that adding the mcTable with the same criteria as the mvTable could be added via union all, especially since I would be using the same fields. It's a bit of a long query, but it works.

select m2.mbID as member_id
    ,t.startDate as start_date
    ,d.tenPayment as member_payment
from mbMaster m2
inner join tenMain d on d.tenID = m2.mbID
inner join
    (
        select b.mvID
            ,min(
                case
                    when c.tenPayment = 0.00
                        and b.mvShortDate = m1.mbShortDate
                        and b.mvPayment = '0000' then m1.mbEffectiveDate end
            ) as startDate
        from mvTable b
        inner join tenMain c on c.tenID = b.mvID
        inner join mbMaster m1 on m1.mbID = b.mvID
        where c.tenStatus = 'A'
            and c.tenPayment = 0.00
            and m1.mbAction in ('1','2','3')
            and not exists
                (
                    select 1
                    from mvTable a
                    where a.mvID = b.mvID
                        and a.mvPayment <> b.mvPayment
                        and a.mvShortDate > b.mvShortDate
                )
        group by b.mvID
    ) t on t.mvID = m2.mbID
        and t.startDate = m2.mbEffectiveDate

union all

select m4.mbID as member_id2
    ,t1.startDate2 as start_date2
    ,h.tenPayment as member_payment2
from mbMaster m4
inner join tenMain h on h.tenID = m4.mbID
inner Join
    (
        select f.mcID
            ,min(
                case
                    when g.tenPayment = 0.00
                        and f.mcShortDate = m3.mbShortDate
                        and f.mcPayment = '000' then m3.mbEffectiveDate
            ) as startDate2
        from mcTable f
        inner join tenMain g on g.tenID = f.mcID
        inner join mbMaster m3 on m3.mbID = f.mcID
        where g.tenStatus = 'A'
            and g.tenPayment = 0.00
            and m3.mbAction in ('1','2','3')
            and not exists
                (
                    select 1
                    from mcTable e
                    where e.mcID = f.mcID
                        and e.mcPayment <> f.mcPayment
                        and e.mcShortDate > f.mcShortDate
                )
        group by f.mcID
    ) t1 on t1.mcID = m4.mbID
        and t1.startDate2 = m4.mbEffectiveDate
order by m2.mbID
Improve this answer

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.