根据频率列获取从开始到结束日期的结果列表

Question

您可以在下面找到我的表格和查询。

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

如您所见，我想根据payment_frequency获得每次出现的结果，直到due_date到达end_date（或在此之前一个月/一年）。

到目前为止，我已经尝试了几件事，但没有任何东西能给我带来我所希望的结果。

Answer 1

我在这里发布了一个示例，说明您可以使用递归 CTE 执行的操作 - 您可以将其用作解决方案的参考：

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