Create DateTime table with Grouped minutes columns - SQL

Please try this for the data-

DECLARE @StartDate AS DATETIME = '2017-11-17'
DECLARE @EndDate AS DATETIME = '2020-11-17'
;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) + (100*d3.Number) + (1000*d4.Number) 
	+ (10000*d5.Number)
	+ (100000*d6.Number)
	+ (1000000*d7.Number)
	Number
    from
    SingleDigits as d1,
    SingleDigits as d2,
    SingleDigits as d3,
    SingleDigits as d4,
	SingleDigits as d5,
	SingleDigits as d6,
	SingleDigits as d7
),
CTE1 AS
(
	SELECT Number,DATEADD(MINUTE,Number-1,@StartDate) N FROM Series 
	WHERE DATEADD(MINUTE,Number-1,@StartDate) <= @EndDate
)
SELECT N
FROM CTE1
ORDER BY Number

Select allOpen in new window

Hello , i have ran that and it only gives me the date column , was looking for the others columns as well , that was in the spreadsheet? did you see it by any chance?

Working on the other columns. :)

ah thanks , also did you see that using the 77 and 240 and 343 minutes they wont be grouped evenly , i want it grouped each day for those minutes so if say there 5 groups of 343minutes in 00:00 - 23:59 the last goup in that might be 320 , i would like the next grouping for new day start from 00:00

that make sense?

thanks again

@Pawan, thanks for your awesome help , I have another question after seeing the results from the script, I have seen that the row number reset after each day which is perfect thanks, but I have noticed that I have missing minutes ( rows ) which return as null, is there a way to basically return last row value up until the next row that has data.

I have attached spreadsheet that shows the results of the script which has missing rows and the next to it the end results would like to achieve.

Are you able to advise please?

thanks
PreviousPrice.xlsx

>>Are you able to advise please?
yes we can do that

SELECT othercolumns,

CASE WHEN marketname IS NULL THEN LAG(marketname) OVER (PARTITION BY CAST(DateKey AS DATE) ORDER BY DateKey) ELSE marketname END marketname

,CASE WHEN ClosedPrice IS NULL THEN LAG(ClosedPrice) OVER (PARTITION BY CAST(DateKey AS DATE) ORDER BY DateKey) ELSE ClosedPrice END ClosedPrice
From
(
    /*Put your existing select here */  
)r

Hello , thanks for script it seems to work for 1 row , but not when there is more than 1 row missing - see attached.

its on sheet 2 :-)
thanks
PreviousPrice.xlsx

Question abandoned.
Provided solution