Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help with this query problem

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
Go to Top of Page

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

Go to Top of Page

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
Go to Top of Page
   

- Advertisement -