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
deanmachine333Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
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

1
deanmachine333Author Commented:
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?
0
Pawan KumarDatabase ExpertCommented:
Working on the other columns. :)
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Pawan KumarDatabase ExpertCommented:
This way you can get the required columns.. You need to add code for the remaining cols.

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, CASE  WHEN DATEDIFF(Minute, CAST(N as date),N) < 30 THEN 1 
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 30 AND DATEDIFF(Minute, CAST(N as date),N) < 60 THEN 2
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 60 AND DATEDIFF(Minute, CAST(N as date),N) < 90 THEN 3
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 90 AND DATEDIFF(Minute, CAST(N as date),N) < 120 THEN 4
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 120 AND DATEDIFF(Minute, CAST(N as date),N) < 150 THEN 5
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 150 AND DATEDIFF(Minute, CAST(N as date),N) < 180 THEN 6
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 180 AND DATEDIFF(Minute, CAST(N as date),N) < 210 THEN 7
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 210 AND DATEDIFF(Minute, CAST(N as date),N) < 240 THEN 8
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 240 AND DATEDIFF(Minute, CAST(N as date),N) < 270 THEN 9
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 270 AND DATEDIFF(Minute, CAST(N as date),N) < 300 THEN 10
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 300 AND DATEDIFF(Minute, CAST(N as date),N) < 330 THEN 11
				WHEN DATEDIFF(Minute, CAST(N as date),N) >= 330 AND DATEDIFF(Minute, CAST(N as date),N) < 360 THEN 12
				END [30min]
		, 
			CASE  WHEN DATEDIFF(Minute, CAST(N as date),N) < 45 THEN 1 
			     WHEN DATEDIFF(Minute, CAST(N as date),N) >= 45 AND DATEDIFF(Minute, CAST(N as date),N) < 90 THEN 2
				 WHEN DATEDIFF(Minute, CAST(N as date),N) >= 135 AND DATEDIFF(Minute, CAST(N as date),N) < 180 THEN 2
				 WHEN DATEDIFF(Minute, CAST(N as date),N) >= 180 AND DATEDIFF(Minute, CAST(N as date),N) < 225 THEN 4				 
			END
			[Group_45min]			
FROM CTE1
ORDER BY Number

Open in new window

1
deanmachine333Author Commented:
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
0
deanmachine333Author Commented:
@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
0
Pawan KumarDatabase ExpertCommented:
>>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
1
deanmachine333Author Commented:
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
0
Pawan KumarDatabase ExpertCommented:
Got it. Please try this -

;WITH CTE AS
(
      /*Put your existing select here */
)
,CTE1 AS
(
      SELECT * , ROW_NUMBER() OVER (PARTITION BY CAST(DateKey AS DATE) ORDER BY DateKey) rnk
      FROM CTE
)
SELECT othercolumns
     ,marketname = CASE WHEN marketname IS NULL THEN (SELECT TOP 1 marketname from CTE1 d WHERE d.rnk = (SELECT MAX(e.rnk) FROM CTE1 e WHERE e.rnk <= c.rnk AND e.marketname IS NOT NULL))
      ,ClosedPrice = CASE WHEN ClosedPrice IS NULL THEN (SELECT TOP 1 ClosedPrice from CTE1 d WHERE d.rnk = (SELECT MAX(e.rnk) FROM CTE1 e WHERE e.rnk <= c.rnk AND e.ClosedPrice IS NOT NULL))
           
FROM CTE1 c
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Provided solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.