Sanilas Sanilas - 19 days ago 5
SQL Question

Get list with results from start to end date based on frequency column

Below you can find my table and the query.

SELECT contract.number
,contract.start_date
,contract.end_date
,contract_details.payment_frequency
,contract_details.amount
FROM contract
JOIN contract_details
ON contract.id = contract_details.contract_id
WHERE contract.status = 'active'

number start_date end_date payment_frequency amount
1000 20.02.2015 20.02.2019 1 260.78
1001 20.02.2015 20.06.2016 12 22.32
1002 15.03.2015 15.03.2059 1 144.00
1003 23.02.2015 23.02.2058 1 300.00
1004 24.02.2015 24.02.2063 1 250.55

1 = yearly payment
12 = monthly payment

Desired Output:

number start_date end_date due_date payment_frequency amount
1000 20.02.2015 20.02.2019 20.02.2015 1 260.78
1000 20.02.2015 20.02.2019 20.02.2016 1 260.78
1000 20.02.2015 20.02.2019 20.02.2017 1 260.78
1000 20.02.2015 20.02.2019 20.02.2018 1 260.78
1001 20.02.2015 20.06.2016 20.02.2015 12 22.32
1001 20.02.2015 20.06.2016 20.03.2015 12 22.32
1001 20.02.2015 20.06.2016 20.04.2015 12 22.32
1001 20.02.2015 20.06.2016 20.05.2015 12 22.32
and so on


As you can see I want to get a result for each occurence based on the payment_frequency until the due_date reaches the end_date (or one month/year before that).

So far I have tried a couple of things but nothing has given me the results I was hoping for.

Answer

i am posting an example here of what you can do by using recursive CTE - you can use this as a reference to your solution:

declare @tbl table (number int,  start_date datetime,  end_date datetime,   

payment_frequency  smallint, amount numeric(6,2))
insert into @tbl
select 1000  ,  '10/02/2015','  10/02/2019',  1,                   260.78
union 
select 1001  ,  '10/02/2015','  10/02/2017',  12,                   230.78


; with CTE as
(select number,start_date,end_date as due_date, end_date from @tbl where end_date>start_date and payment_frequency=1
union all
select c.number,c.start_date,dateadd(yy,-1,c.due_date) as due_date,t.end_date from CTE c join @tbl t on t.number=c.number 
where c.due_date<=t.end_date and dateadd(yy,-1,c.due_date)>c.start_date and  t.payment_frequency=1 
)
select *
from cte


; with CTE as
(select number,start_date,end_date as due_date, end_date from @tbl where end_date>start_date and payment_frequency=12
union all
select c.number,c.start_date,dateadd(mm,-1,c.due_date) as due_date,t.end_date from CTE c join @tbl t on t.number=c.number 
where c.due_date<=t.end_date and dateadd(mm,-1,c.due_date)>c.start_date and  t.payment_frequency=12
)
select *
from cte
Comments