Author |
Topic |
kkbearjj
Starting Member
4 Posts |
Posted - 2015-04-10 : 11:17:18
|
Hi, I need help to finish my project ASAP. My task is to figure out defects in this Loan_Status_Tbl.
When Current_Principal= 0, Loan_status should be populated from previous records. For example, when Loan_Nbr 00001's current_Principal=0, it's loan_status is not populated from it's 2010-12-30 record, and 00002 is good. I want to know which items are not good, and report the results to engineer to modify the wrong record in DW.
ID LOAN_NBR CURRENT_PRINCIPAL LOAD_DT LOAN_STATUS
1 00001 1000 2010-12-5 APPROVED
2 00001 800 2010-12-30 APPROVED
3 00001 0 2011-3-1 NULL
4 00002 500 2012-5-1 APPROVED
5 00002 0 2012-6-12 APPROVED |
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-04-10 : 11:32:38
|
[code] ;WITH cteSample AS(SELECT ID,LOAN_NBR,CURRENT_PRINCIPAL,LOAD_DT,LOAN_STATUS FROM (VALUES(1,'00001',1000,'2010-12-5','APPROVED') ,(2,'00001',800,'2010-12-30','APPROVED') ,(3,'00001',0,'2011-3-1', NULL) ,(4,'00002',500,'2012-5-1','APPROVED') ,(5,'00002',0,'2012-6-12','APPROVED')) A (ID,LOAN_NBR,CURRENT_PRINCIPAL,LOAD_DT,LOAN_STATUS) ) SELECT ID ,LOAN_NBR ,CURRENT_PRINCIPAL ,LOAD_DT ,A.LOAN_STATUS ,B.LOAN_STATUS FROM cteSample AS A OUTER APPLY ( SELECT TOP(1) B.LOAN_STATUS FROM cteSample AS B WHERE A.LOAN_NBR =B.LOAN_NBR AND A.ID>B.ID )B WHERE A.CURRENT_PRINCIPAL =0 AND ( (A.LOAN_STATUS <> B.LOAN_STATUS) OR (A.LOAN_STATUS IS NULL AND B.LOAN_STATUS IS NOT NULL) ) ORDER BY A.ID [/code]
output: [code] ID LOAN_NBR CURRENT_PRINCIPAL LOAD_DT LOAN_STATUS LOAN_STATUS 3 00001 0 2011-3-1 NULL APPROVED [/code]
sabinWeb MCP |
 |
|
kkbearjj
Starting Member
4 Posts |
Posted - 2015-04-10 : 12:10:17
|
Thank you very much. But if the situation like this,
Loan_Nbr Loan_status Principal 00001 Pending 500 00001 Approved 500 00001 Approved 0
Your query will also pick up 00001 as defect, but actually it's good. How should I modify this query?
quote: Originally posted by stepson
;WITH cteSample AS(SELECT ID,LOAN_NBR,CURRENT_PRINCIPAL,LOAD_DT,LOAN_STATUS FROM (VALUES(1,'00001',1000,'2010-12-5','APPROVED') ,(2,'00001',800,'2010-12-30','APPROVED') ,(3,'00001',0,'2011-3-1', NULL) ,(4,'00002',500,'2012-5-1','APPROVED') ,(5,'00002',0,'2012-6-12','APPROVED')) A (ID,LOAN_NBR,CURRENT_PRINCIPAL,LOAD_DT,LOAN_STATUS) ) SELECT ID ,LOAN_NBR ,CURRENT_PRINCIPAL ,LOAD_DT ,A.LOAN_STATUS ,B.LOAN_STATUS FROM cteSample AS A OUTER APPLY ( SELECT TOP(1) B.LOAN_STATUS FROM cteSample AS B WHERE A.LOAN_NBR =B.LOAN_NBR AND A.ID>B.ID )B WHERE A.CURRENT_PRINCIPAL =0 AND ( (A.LOAN_STATUS <> B.LOAN_STATUS) OR (A.LOAN_STATUS IS NULL AND B.LOAN_STATUS IS NOT NULL) ) ORDER BY A.ID
output:
ID LOAN_NBR CURRENT_PRINCIPAL LOAD_DT LOAN_STATUS LOAN_STATUS 3 00001 0 2011-3-1 NULL APPROVED
sabinWeb MCP
|
 |
|
stepson
Aged Yak Warrior
545 Posts |
Posted - 2015-04-11 : 01:19:14
|
I forgot to add ORDER BY B.ID DESC here is the code:
;WITH cteSample AS(SELECT ID,LOAN_NBR,CURRENT_PRINCIPAL,LOAD_DT,LOAN_STATUS FROM (VALUES(1,'00001',1000,'2010-12-5','APPROVED') ,(2,'00001',800,'2010-12-30','APPROVED') ,(3,'00001',0,'2011-3-1', NULL) ,(4,'00002',500,'2012-5-1','APPROVED') ,(5,'00002',0,'2012-6-12','APPROVED') ,(6,'00003',850,'2012-1-1','Pending') ,(7,'00003',500,'2012-2-1','Approved') ,(8,'00003',0,'2012-3-2','Approved') ) A (ID,LOAN_NBR,CURRENT_PRINCIPAL,LOAD_DT,LOAN_STATUS) ) SELECT ID ,LOAN_NBR ,CURRENT_PRINCIPAL ,LOAD_DT ,A.LOAN_STATUS ,B.LOAN_STATUS FROM cteSample AS A OUTER APPLY ( SELECT TOP(1) B.LOAN_STATUS FROM cteSample AS B WHERE A.LOAN_NBR = B.LOAN_NBR AND A.ID > B.ID ORDER BY B.ID DESC )B WHERE A.CURRENT_PRINCIPAL =0 AND ( (A.LOAN_STATUS <> B.LOAN_STATUS) OR (A.LOAN_STATUS IS NULL AND B.LOAN_STATUS IS NOT NULL) ) ORDER BY A.ID
output:
ID LOAN_NBR CURRENT_PRINCIPAL LOAD_DT LOAN_STATUS LOAN_STATUS 3 00001 0 2011-3-1 NULL APPROVED [/code] [/code]
sabinWeb MCP |
 |
|
|
|
|