Select rows where main value has disabled status and sub value is active-CodePudding

I have a table containing customer agreement numbers and a status field indicating whether that agreement is active or not - 1 for active, 0 for disabled.

A main customer number contains 5 digits, from which other subagreements can be made. These other agreements are characterized by a 10 digit number, the first 5 coming from the main number and the last 5 autogenerated.

Note that not all main agreements necessarily have subagreements.

Heres a simplified snippet of the table I currently get from my query:

 ------------- ---------- ------------ -- 
|   CustNumber| CustName | CustStatus |  |
 ------------- ---------- ------------ -- 
|12345        | Cust1    |          1 |  |
|1234500001   | Cust1    |          1 |  |
|1234500002   | Cust1    |          0 |  |
|12346        | Cust2    |          0 |  |<---
|1234600001   | Cust2    |          1 |  |<---
|1234600002   | Cust2    |          0 |  |
 ------------- ---------- ------------ --

Query:

 SELECT 
     custnumber,
     custstatus,
     custname
  FROM table
  WHERE LEFT(custnumber, 5) IN (
     SELECT LEFT(custnumber, 5)
         FROM   table
         GROUP  BY LEFT(custnumber, 5)
         HAVING Count(*) > 1
      )
  ORDER  BY custnumber,
            custstatus DESC;

From here I'm pretty lost. I'm thinking something along the lines of an inner join on a subquery but I'm really not sure.

What I'm looking for is a query that selects rows with subagreement numbers that are active but where the main agreement number is disabled.

I'm new to SQL and have spend a good while searching around for similar questions, but I actually don't know how to describe this problem in a google-friendly manner.

CodePudding user response：

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=5873044787e5fd3f32f7648dbc54a7b0

with data (CustNumber, CustName, CustStatus) as(
    Select '12345'        ,'Cust1',1  union all
    Select '1234500001'   ,'Cust1',1  union all
    Select '1234500002'   ,'Cust1',0  union all
    Select '12346'        ,'Cust2',0  union all
    Select '1234600001'   ,'Cust2',1  union all
    Select '1234600002'   ,'Cust2',0
)
,subagg (k,CustNumber, CustName, CustStatus) as(
    select Left(CustNumber,5) k,CustNumber, CustName, CustStatus 
    from data
    where len(CustNumber)=10
    and CustStatus = 1
)
select s.CustNumber ActiveSunCustomer, d.CustNumber InactivePrimaryCustomer
from subagg s
join data d on d.CustNumber=s.k and d.CustStatus = 0

CodePudding user response：

Join the table with itself - I am using a WITH clause for readability, but that is not necessary - and check the statuses.

with main_rows as 
(
  select custnumber as main_number, custname, custstatus 
  from mytable 
  where length(custnumber) = 5
)
, sub_rows as 
(
  select 
    left(custnumber, 5) as main_number,
    right(custnumber, 5) as sub_number,
    custname,
    custstatus 
  from mytable 
  where length(custnumber) = 10
)
select
  main_number,
  m.custname as main_name,
  s.sub_number,
  s.custname as sub_name
from main_rows m
join sub_rows s using (main_number)
where m.custstatus = 0 and s.custstatus = 1
order by main_number, s.sub_number;

And here is the same thing, but shorter and just not as talkative :-)

select *
from mytable m
join mytable s on s.custnumber like m.custnumber || '_____'
where m.custstatus = 0 and s.custstatus = 1
order by s.custnumber;