sqlsql-serversql-server-2012

Exclude Records With Partial Matching Conditions


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

Solution

  • 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')
       )
    );