question

ScottGraham-8086 avatar image
0 Votes"
ScottGraham-8086 asked MelissaMa-msft commented

SQL query help

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

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @ScottGraham-8086,

Could you please provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @ScottGraham-8086,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.