Help with this query problem - SQL Server Forums

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources