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

General SQL Server Forums

New to SQL Server Programming

Help with this query problem

Author

Topic

kkbearjj
Starting Member

4 Posts

Posted - 2015-04-10 : 15:10:20

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,00003 are 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

6 00003 850 2012-1-1 Pending

7 00003 500 2012-2-1 Approved

8 00003 0 2012-3-2 Approved

ElenaSTL
Starting Member

10 Posts

Posted - 2015-04-10 : 16:54:06

CREATE TABLE #tbl1 (ID Int NOT NULL, LOAN_NBR int,CURRENT_PRINCIPAL int, LOAD_DT Datetime, LOAN_STATUS varchar(10))
INSERT INTO #tbl1 VALUES (1,1,1000, '2010-12-5', 'APPROVED')
INSERT INTO #tbl1 VALUES (2,1,800, '2012-02-02', 'APPROVED')
INSERT INTO #tbl1 VALUES (3,1,0, '2012-02-03',NULL)
INSERT INTO #tbl1 VALUES (4,2,500, '2012-02-04', 'APPROVED')
INSERT INTO #tbl1 VALUES (5,2,0, '2012-02-05', 'APPROVED')
INSERT INTO #tbl1 VALUES (6,3,850, '2012-02-06',' Pending')
INSERT INTO #tbl1 VALUES (7,3,500, '2012-02-07', 'APPROVED')
INSERT INTO #tbl1 VALUES (8,3,0, '2012-02-08', 'APPROVED')

select * from #tbl1
--drop table #tbl1

CREATE TABLE #tbl2 (RowN Int NOT NULL,ID Int NOT NULL, LOAN_NBR int,CURRENT_PRINCIPAL int, LOAD_DT Datetime, LOAN_STATUS varchar(10))
INSERT INTO #tbl2
SELECT ROW_NUMBER() OVER(PARTITION BY LOAN_NBR ORDER BY LOAD_DT), ID, LOAN_NBR,CURRENT_PRINCIPAL, LOAD_DT, LOAN_STATUS
FROM #tbl1;
select * from #tbl2

select *
from #tbl2 as t1 inner join #tbl2 as t2
on t1.LOAN_NBR = t2.LOAN_NBR
and t1.RowN = t2.RowN - 1
and t2.CURRENT_PRINCIPAL = 0
and (t1.LOAN_STATUS <> t2.LOAN_STATUS
or (t2.LOAN_STATUS is null and not t1.LOAN_STATUS is null))

drop table #tbl1
drop table #tbl2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources