asked on

Create DateTime table with Grouped minutes columns - SQL

Hello,

Im trying to create a SQL table that has a datetime (date , hour and minute only) column starting from say 2017-11-17T00:00 and columns with heading say 30min , 45min, 77min, 240min, 343min which is basically grouping the datetime column in those specific minutes .

But the groupings only happen between 00:00 and 23:59 each day , and then starts the grouping again at 00:00 onwards .

have spreadsheet with starting what it should look like , would like to have about 3 years datetime from 2017-11-17 please :-)
datetimeGrouped.xlsx

Pawan Kumar

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

Open in new window

deanmachine333

ASKER

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?

Pawan Kumar

Working on the other columns. :)

SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

deanmachine333

ASKER

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

deanmachine333

ASKER

@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

Pawan Kumar

>>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

deanmachine333

ASKER

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

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Question abandoned.
Provided solution