Deploy rows in a date range

Navigation
0

I have a question regarding SQL SERVER and I needed your help. I have a Query that returns a line with a number, Start_Date and End_Date.

Inquiry: 

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

Result: 

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

I wanted to show a record for each day until the last one was equal to date_fim, that is: 

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
    
asked by anonymous 01.06.2018 / 20:10

1 answer

0
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)
    
01.06.2018 / 22:30
Run php artisan in a different directory Error when querying API in Angular