September 20, 2018 at 8:59 am
I have a loan that is modified several times on several dates. I need to find the Principle balance , before and after that modified date.
Example:
Loan PrincipalBalance LastRunDate Modified date
12345 0 9/18/2018 9/13/2018
12345 0 9/17/2018 9/10/2018
12345 0 9/14/2018 5/8/2017
12345 0 9/13/2018
12345 0 9/12/2018
12345 0 9/11/2018
12345 0 9/10/2018
12345 0 9/8/2018
12345 0 5/9/2017
12345 17172.8 5/8/2017
12345 17172.8 5/6/2017
12345 17172.8 5/4/2017
12345 17172.8 5/3/2017
The last rundate is our system date which updates the loan every day. Modified date is when the loan is modified .
for above example :
9/13/2018 is modified date : Principle balance before modifed date 9/13/2018 ie. on 9/12/2018 is 0 after modifed is on 9/12/2018 0
Loannumber modified date principle balance before principle balance after
12345 9/13/2018 0 0
12345 9/10/2018 0 0
12345 5/8/2017 0 17172.8
How can i achieve this through Tsql?
Thanks in advance.
September 20, 2018 at 10:14 am
komal145 - Thursday, September 20, 2018 8:59 AMI have a loan that is modified several times on several dates. I need to find the Principle balance , before and after that modified date.Example:
Loan PrincipalBalance LastRunDate Modified date
12345 0 9/18/2018 9/13/2018
12345 0 9/17/2018 9/10/2018
12345 0 9/14/2018 5/8/2017
12345 0 9/13/2018
12345 0 9/12/2018
12345 0 9/11/2018
12345 0 9/10/2018
12345 0 9/8/2018
12345 0 5/9/2017
12345 17172.8 5/8/2017
12345 17172.8 5/6/2017
12345 17172.8 5/4/2017
12345 17172.8 5/3/2017The last rundate is our system date which updates the loan every day. Modified date is when the loan is modified .
for above example :
9/13/2018 is modified date : Principle balance before modifed date 9/13/2018 ie. on 9/12/2018 is 0 after modifed is on 9/12/2018 0
Loannumber modified date principle balance before principle balance after
12345 9/13/2018 0 0
12345 9/10/2018 0 0
12345 5/8/2017 0 17172.8
How can i achieve this through Tsql?Thanks in advance.
here is the Query :
DROP TABLE #modDate
GO
CREATE TABLE #modDate
(
Loan varchar(10) ,
priciplebalance float,
lastrundate date
)
Insert into #modDate
VALUES ('12345' , 0 , '9-18-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '9-17-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '9-14-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '9-13-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '9-12-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '9-11-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '9-10-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '9-08-2018')
GO
Insert into #modDate
VALUES ('12345' , 0 , '5-09-2018')
GO
Insert into #modDate
VALUES ('12345' , 17172.8 , '5-08-2018')
GO
Insert into #modDate
VALUES ('12345' , 17172.8 , '5-06-2018')
GO
Insert into #modDate
VALUES ('12345' , 17172.8 , '5-04-2018')
GO
Insert into #modDate
VALUES ('12345' , 17172.8 , '5-03-2018')
September 21, 2018 at 3:27 am
It's not totally clear what you want, but here are a couple of ideas:
(I've changed the column name to "PrincipalBalance".)
-- Show only LastRunDate where PrincipalBalance changes:
SELECT D1.Loan, D1.LastRunDate, [ValueBefore] = D2.PrincipalBalance, [ValueAfter] = D1.PrincipalBalance
FROM #modDate D1
LEFT JOIN #modDate D2 ON D1.LastRunDate = DATEADD(dd, 1, D2.LastRunDate)
WHERE D1.PrincipalBalance <> D2.PrincipalBalance
ORDER BY D1.LastRunDate
-- Show all LastRunDate where PrincipalBalance changes:
-- You'd need to deal with missing dates, if you have missing dates in the real file...
SELECT D1.Loan, D1.LastRunDate, [ValueBefore] = D2.PrincipalBalance, [ValueAfter] = D1.PrincipalBalance
FROM #modDate D1
LEFT JOIN #modDate D2 ON D1.LastRunDate = DATEADD(dd, 1, D2.LastRunDate)
ORDER BY D1.LastRunDate
These should run on SQL 2008. For later versions you can use the LAG() & LEAD() functions.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply