SQL query help

Question

Hi everyone,

I am writing an sql query and am stuck on the logic. I am working on a remediation project where I want to apply a historical discount to customers floating or variable Home Loan that can apply at different times.

So the discount on their HL rate maybe 0.5% on their rate (say 4.99%) over a period of time. The trouble is multiple customers (up to 3) can be joined on the HL and each customer can be entitled to discounts at different times.

Lets say a customer has a Home Loan from 2010 to present which is floating. There are 3 customers attached to this Home Loan. Customer 1 has a discount from March 2011 to April 2013. Customer 2 has a discount from April 2012 to April 2015 and customer 3 has a discount from Sep 2018 until present. I'd like to apply the discount to the balance of the Home Loan from March 2011 to April 2015 and then again from Sep 2018 until present. Has anyone encountered this problem before or know of an example online tackling a similar issue that I can base my code off?

Thanks for any help

Answer 1

Hi @Scott Graham ,

Welcome to Microsoft Q&A!

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Please also refer below whether it is helpful:

create table Remediation   
(  
LoanName varchar(10),  
CustomerName varchar(20),  
Startdate date,  
Enddate date  
)  
  
insert into  Remediation values  
('Loan1','Cust1','2011-03-01','2013-04-01'),  
('Loan1','Cust2','2012-04-01','2015-04-01'),  
('Loan1','Cust3','2018-09-01',NULL)  
  
;with cte as (  
select *,LEAD(Startdate) over (partition by LoanName order by Startdate) as ned  
from Remediation  
),cte1 as (   
select LoanName,CustomerName,Startdate ,case when Enddate>ned then ned else Enddate end Enddate  
  from cte)  
 ,cte2 as (  
     select LoanName,Startdate,Enddate  
     from cte1  
     union all  
     select  t.LoanName,cte2.Startdate, t.Enddate  
     from cte2  
     join cte1 t on  cte2.Enddate = t.Startdate and cte2.LoanName=t.LoanName   
 )  
 , cte3 as (  
     select *, rn = row_number() over (partition by LoanName,Enddate order by Startdate)  
     from cte2  
 )  
 select  LoanName,min(Startdate) Startdate, max(Enddate) Enddate  
 from cte3  
 where rn=1  
 group by LoanName,Startdate  

Output:

LoanName	Startdate	Enddate  
Loan1	2011-03-01	2015-04-01  
Loan1	2018-09-01	NULL  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.