• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 48
  • Last Modified:

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
0
deanmachine333
Asked:
deanmachine333
  • 6
  • 4
2 Solutions
 
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
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
 
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Provided solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now