October 7, 2015 at 10:44 am
I created a script to return all the loans with disposition code 'PS' happened immediately after disposition code ('PA','PM','NP','CI','CR','CL'). Disposition code ('PA','PM','NP','CI','CR','CL') has to happen between '2015-02-15' and '2015-07-15'. And if a loan has PA/PM/NP/CI/CR/CL and PS on the same date, we need to capture this one, too.
Why my script can't catch Loan 11613577 and 11613579, with Disposition_Date on 2015-07-15? Even though I changed filter condition to a.Disposition_Dt between '2015-02-14' and '2015-07-16'
Why my script can't catch Loan 14364944? I know because "PM" and "PS" happen in the same date. How can I catch this case?
I know my script is too complex, and missed some cases. Anyone can help me modify it in a easier way? Thank you.
Sample Data
Customer_Nbr_Txt Loan_NbrDisposition_DtDisposition_Code_Txt
007849116135772015-07-21PS
007849116135772015-07-15NP
007849116135792015-07-21PS
007849116135792015-07-15NP
008120143649442015-05-27PM
008120143649442015-05-27PS
with cte1
as
(
SELECT *,
ROW_NUMBER()over(partition by a.loan_nbr
order by a.Disposition_Dt desc)rn
FROM [DW_ODS].[dbo].[WMS_LoanDispositionData] a
)
,
cte2
as
(
SELECT *,
ROW_NUMBER()over(partition by a.loan_nbr
order by a.Disposition_Dt desc)rn
FROM [DW_ODS].[dbo].[WMS_LoanDispositionData] a
where a.Disposition_Dt between '2015-02-15' and '2015-07-15'
)
select cte1.Customer_Nbr_Txt,b.[Customer Name - Legal],cte1.Loan_Nbr,b.[Supplier Name],b.[Collateral Code],cte2.Disposition_Dt as [Disposition Code 1 Date (Inspection Date)] ,cte2.Disposition_Code_Txt as [Disposition Code 1],cte1.Disposition_Dt as [Disposition Code 2 ("PS" code) Date]
from cte1
join cte2 on cte1.Loan_Nbr=cte2.Loan_Nbr
where (cte1.Disposition_Code_Txt='PS'and cte1.rn=1)
and (cte2.Disposition_Code_Txt in ('PA','PM','NP','CI','CR','CL') and cte2.rn=2)
October 7, 2015 at 10:52 am
It's likely this code
and (cte2.Disposition_Code_Txt in ('PA','PM','NP','CI','CR','CL') and cte2.rn=2)
The row number in the CTE is just based on loan number so it's likely the loan with the PM code is not getting assigned to row number 2. Do you have a more granular field you can use to determine the order of the records, if you just have date and both loans happen on the same day the order that the loans show up in will be ambiguous.
October 7, 2015 at 3:35 pm
It appears that the CTE2 you have is excluding the first two loans, since the have disposition dates outside the ranges you've identified. In my example, I've made two assumptions, 1) the 'PS' record could appear outside the date window and 2) the listed Disposition codes represent the entire set.
Here's my code sample "as is", I've made no attempts to standardize, tuning or otherwise "clean-up" anything.
Hope this helps -
IF NOT EXISTS(SELECT 1 FROM sys.objects where name = 'WMS_LoanDispositionData')
BEGIN
CREATE TABLE WMS_LoanDispositionData
(
Customer_Nbr_TxtVARCHAR(255),
Loan_NbrBIGINT,
Disposition_DtDATE,
Disposition_Code_Txt VARCHAR(255)
)
INSERT INTO WMS_LoanDispositionData
VALUES
('007849', '11613577', '2015-07-21', 'PS'),
('007849', '11613577', '2015-07-15', 'NP'),
('007849', '11613579', '2015-07-21', 'PS'),
('007849', '11613579', '2015-07-15', 'NP'),
('008120', '14364944', '2015-05-27', 'PM'),
('008120', '14364944', '2015-05-27', 'PS')
END
;WITHcte1 AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY a.loan_nbr
ORDER BY a.Disposition_Dt DESC, a.Disposition_Code_Txt DESC) rn
FROM WMS_LoanDispositionData a
)
SELECT *
FROM cte1 C1a
WHERE Disposition_Code_Txt = 'PS'
AND EXISTS (SELECT 1 FROM cte1 C1b
WHERE C1b.Customer_Nbr_Txt = c1a.Customer_Nbr_Txt
ANDC1b.Loan_Nbr = C1a.Loan_Nbr
AND C1b.Disposition_Code_Txt IN ('CI', 'CL', 'CR', 'NP', 'PA', 'PM')
AND C1b.Disposition_Dt BETWEEN '2015-02-15' AND '2015-07-15'
AND (C1b.rn - C1a.rn) = 1
)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply