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