Tsql query

• komal145

  SSCrazy Eights

  Points: 9920

  More actions

  September 20, 2018 at 8:59 am

  #363018

  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.

• komal145

  SSCrazy Eights

  Points: 9920

  More actions

  September 20, 2018 at 10:14 am

  #2006449

  komal145 - Thursday, September 20, 2018 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.

  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')

• laurie-789651

  SSCertifiable

  Points: 7680

  More actions

  September 21, 2018 at 3:27 am

  #2006528

  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.