Unfold lines in a date range

Question

I have a question regarding SQL SERVER and I needed your help. I have a Query that returns me a line with a number, Start_date and End_date.

Consultation:

select 123456 as Numero, a.Data_inicio, a.Data_fim
from CLI_INTERNAMENTOS_UTENTES a
where a.Data_inicio between '20171001' and '20180601'

Upshot:

Numero    Data_inicio Data_fim
123456    01/10/2017  01/06/2018

I wanted to show a record for each day until the last is equal enddate, ie:

Numero  Data_inicio Data_fim    Data(novo campo)
1.123456    01/10/2017  01/06/2018  01/10/2017
2.123456    01/10/2017  01/06/2018  02/10/2017
3.123456    01/10/2017  01/06/2018  03/10/2017
4.123456    01/10/2017  01/06/2018  04/10/2017
5.123456    01/10/2017  01/06/2018  05/10/2017
6.123456    01/10/2017  01/06/2018  06/10/2017
7.123456    01/10/2017  01/06/2018  07/10/2017
8.123456    01/10/2017  01/06/2018  08/10/2017
9.123456    01/10/2017  01/06/2018  09/10/2017
10.123456   01/10/2017  01/06/2018  10/10/2017
11.123456   01/10/2017  01/06/2018  11/10/2017
12.123456   01/10/2017  01/06/2018  12/10/2017

Answer 1

WITH q (data_inicio, data_fim, data) AS
( 
    SELECT a.data_inicio, a.data_fim, a.data_inicio AS data
    FROM CLI_INTERNAMENTOS_UTENTES a

    UNION ALL

    SELECT a.data_inicio, a.data_fim, DATEADD(day, 1, q.data)
    FROM CLI_INTERNAMENTOS_UTENTES a
    INNER JOIN q ON q.data_inicio = a.data_inicio AND q.data_fim = a.data_fim
    WHERE a.data_fim > q.data
) 
SELECT CAST(ROW_NUMBER() OVER (ORDER BY data ASC) AS varchar) + '.123456' AS Numero, data_inicio, data_fim, data
FROM q
WHERE data_inicio BETWEEN '2017-10-01' AND '2018-06-01'
OPTION (MAXRECURSION 32767)