SQL Server Exclude Records With Partial Matching Conditions

xggvc2p6 于 2023-11-16 发布在其他

关注(0)|答案(2)|浏览(109)

I have two tables, Person and Loan_Dtl and I want my results to show only persons where all of their loans match the criteria below. If the person has a mix of loans where some match these conditions and others do not, then the person should not show up in the results.

l.loan_type_cd IN ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
AND (l.opb_amt + l.oib_amt) > 0
AND l.loan_status_cd = 'RP'
AND l.repymt_plan_type_cd IN ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 'IB', 'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
l.loan_holder_cd IN ('578', '580', '500', '507', '512')

My code below works to show persons who have a mix of some matching loans. In the example data, the D2', 'RP', 'I5', 250, 50 is the only loan included in the results, but this person (2544541) should not be included at all in the results because he has other loans which do not match. I need only persons where all loans match. person 2544542 should show up in the results since he has only matching loans.

SELECT 
  COUNT(DISTINCT p.person_id) AS Borrower_Count   
 ,SUM(l.opb_amt + l.oib_amt) as balance 
FROM person p INNER JOIN 
 loan_dtl l on p.person_id = l.brwr_person_id 
WHERE  
  NOT EXISTS  
            (SELECT 1 
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id 
              AND l2.loan_type_cd NOT IN 
  ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
            ) 
            
  AND NOT EXISTS  
            (SELECT 1 
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id 
              AND (l.opb_amt + l.oib_amt) = 0 
            )

    AND NOT EXISTS 
            (SELECT 1 
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id
              AND l.loan_status_cd NOT IN ('RP')
          ) 

 AND NOT EXISTS 
            (SELECT 1
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id
              AND l.repymt_plan_type_cd NOT IN ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 
              'IB', 'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
            )
            
  AND NOT EXISTS 
            (SELECT 1
              FROM loan_dtl l2 
              WHERE l.loan_id = l2.loan_id 
              AND l.loan_holder_cd NOT IN ('578', '580', '500', '507', '512')
            )

Tables and data:

CREATE TABLE [dbo].[Loan_dtl](
[loan_id] [int] NULL,
[brwr_person_id] [int] NULL,
[loan_holder_cd] [int] NULL,
[loan_type_cd] [varchar](50) NULL,
[loan_status_cd] [varchar](50) NULL,
[REPYMT_PLAN_TYPE_CD] [varchar](50) NULL,
[OPB_AMT] [int] NULL,
[OIB_AMT] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Person](
[person_id] [int] NULL
) ON [PRIMARY]
GO

INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (124677158, 
 2544541, 755, N'CL', NULL, NULL, 25824, 3261)
 GO
 INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
 [loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (124919090, 
2544541, 755, N'CL', NULL, NULL, 37708, 4761)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070274, 
2544541, 507, N'D2', N'RP', N'I5', 250, 50)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070266, 
2544541, 507, N'SF', NULL, N'IB', 737, 189)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070269, 
2544541, 507, N'D1', NULL, N'IB', 0, 0)
GO
INSERT [dbo].[Loan_dtl] ([loan_id], [brwr_person_id], 
[loan_holder_cd], [loan_type_cd], [loan_status_cd], 
[REPYMT_PLAN_TYPE_CD], [OPB_AMT], [OIB_AMT]) VALUES (102070280, 
2544542, 507, N'D2', N'RP', N'I5', 950, 5)
GO
INSERT [dbo].[Person] ([person_id]) VALUES (2544541)
GO
INSERT [dbo].[Person] ([person_id]) VALUES (2544542)
GO

sql-server

来源：https://stackoverflow.com/questions/77352135/exclude-records-with-partial-matching-conditions

2条答案

按热度按时间

q9yhzks01#

I think the main problem with your logic is that your NOT EXISTS clause is correlated by loan_id . So all the loans from any given person but that don't get excluded by NOT EXISTS will still come back. You need to change that correlation to brwr_person_id instead. I think it can be simplified to a simple NOT EXISTS clause:

WHERE NOT EXISTS  
(
  SELECT 1 FROM loan_dtl l2 
    WHERE l.brwr_person_id = l2.brwr_person_id
    AND 
    (
      l2.loan_type_cd NOT IN
         ('D0','D1','D2','D3','D5','D6','D7','D8','D9'))
      OR (l.opb_amt + l.oib_amt) = 0 
      OR l.loan_status_cd NOT IN ('RP')
      OR l.repymt_plan_type_cd NOT IN 
         ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 'IB', 
          'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
      OR l.loan_holder_cd NOT IN 
         ('578', '580', '500', '507', '512')
   )
);

Though the following variation would be a little more optimizer-friendly (eliminates some costly SORT operators):

SELECT 
  COUNT(p.person_id) AS Borrower_Count,
  SUM(cap.balance) AS balance
FROM dbo.person p CROSS APPLY
(
  SELECT SUM(l.opb_amt + l.oib_amt) as balance 
  FROM dbo.loan_dtl l 
  WHERE p.person_id = l.brwr_person_id 
) AS cap
WHERE NOT EXISTS  
(
  SELECT 1 FROM dbo.loan_dtl l2 
    WHERE p.person_id = l2.brwr_person_id
    AND 
    (
      l2.loan_type_cd NOT IN
         ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
      OR (l2.opb_amt + l2.oib_amt) = 0 
      OR l2.loan_status_cd NOT IN ('RP')
      OR l2.repymt_plan_type_cd NOT IN 
         ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 'IB', 
          'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
      OR l2.loan_holder_cd NOT IN 
         ('578', '580', '500', '507', '512')
   )
);

Example db<>fiddle

赞(0）回复(0）举报 2023-11-16

ycggw6v22#

You can filter the main query to identify the wanted record then check the person_id for the opposite conditions with DeMorgan's Law.

SELECT COUNT(DISTINCT p.person_id) AS Borrower_Count   
      ,SUM(l.opb_amt + l.oib_amt) as balance 
  FROM [dbo].[Person] p
    INNER JOIN Loan_dtl l ON p.person_id = l.brwr_person_id
 WHERE l.loan_type_cd IN ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
   AND (l.opb_amt + l.oib_amt) > 0
   AND l.loan_status_cd = 'RP'
   AND l.repymt_plan_type_cd IN ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 'IB', 'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
   AND l.loan_holder_cd IN ('578', '580', '500', '507', '512')

   AND p.person_id NOT IN (
   --DeMorgan's Law, to take the opposite logic
SELECT brwr_person_id
  FROM Loan_dtl
 WHERE loan_type_cd NOT IN ('D0','D1','D2','D3','D5','D6','D7','D8','D9')
   OR (opb_amt + oib_amt) <= 0
   OR loan_status_cd <> 'RP'
   OR repymt_plan_type_cd NOT IN ('C1', 'C2', 'C3', 'I3', 'I4', 'I5', 'IB', 'IC', 'IL', 'IS', 'J5', 'P1', 'PA', 'RA')
   OR loan_holder_cd NOT IN ('578', '580', '500', '507', '512')
   )

赞(0）回复(0）举报 2023-11-16

我来回答

SQL Server Exclude Records With Partial Matching Conditions

2条答案

相关问题

热门标签

最新问答