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

- Advertisement -