Group data into 4 hour interval and dont sum or avg a column - sql server script

Hello, I have this query that has data for every minute which I have attached some data of the table.

I want to group this into 4 hours starting from 00:00, but I don't want to sum or average the value in the column [Last] I just want to return that value in the [Last] column as it is at that specific grouped hour.

SELECT b.[timestamp], b.[id], b.[Date], b.[HourMin], b.[Min], b.[Last]
FROM [vw_BassLineDataTimestamp] b

Open in new window

So should return this instead

timestamp       id        Date        HourMin Min      Last
2017-11-20T08:00:48.467      BTC-LTC_2017-11-20_08:00      2017-11-20      08:00        0        0.00890995
2017-11-20T04:00:50.583      BTC-LTC_2017-11-20_04:00      2017-11-20      04:00        0        0.009015
2017-11-20T00:00:51.573      BTC-LTC_2017-11-20_00:00      2017-11-20      00:00        0        0.0089

Thanks in advance.

Also if possible could I amend the hour interval so instead of 4 hours (240 min) to any say like 5.71667 hours (343min) or any interval would be great.

Kind regards
LTC1minData.xlsx

Microsoft SQL ServerSQL

Last Comment

Pawan Kumar

8/22/2022 - Mon

ASKER CERTIFIED SOLUTION

Pawan Kumar

11/20/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Olaf Doschke

11/20/2017

The expression DateDiff(minute,0, somedatetime)/intervallengthinminutes will result in an int giving all records of the same interval the same group number. You don't need to group by that number.

Notice: MSSQL keeps results in the same data type, so dividing an int by another int does not result in a float, this doesn't need Round(DateDiff(minute,0, somedatetime)/intervallengthinminutes,0), but in other RDBMS you'd need something along these lines (and whatever datediff() another RDBMS would need).

Now it's a bit unclear what you want, do you want the value of [Last] as it is in the last record of the group? Then you need to look for the record with the max(somedatetime) within that group (that's received by grouping) and pick out the full record of that somedateetime=maxsomedatetime of the group.

That could be done with window function and partitioning using LAST_VALUE ( [Last]) OVER ( partition by DateDiff(minute,0, somedatetime)/intervallengthinminutes order by somedatetime)

Bye, Olaf.

deanmachine333

11/20/2017

ASKER

Hello ,

I was looking to pick out the Column value [Last] for every 4 hours starting @ 00:00 , so be

timestamp id Date HourMin Min Last
2017-11-20T08:00:48.467 BTC-LTC_2017-11-20_08:00 2017-11-20 08:00 0 0.00890995
2017-11-20T04:00:50.583 BTC-LTC_2017-11-20_04:00 2017-11-20 04:00 0 0.009015
2017-11-20T00:00:51.573 BTC-LTC_2017-11-20_00:00 2017-11-20 00:00 0 0.0089

deanmachine333

11/20/2017

ASKER

Im basically looking to take the minute rows to be grouped into 4 hours on the hour starting from 00H00min but would like to return the actual value at each of the 4 hours intervales. So for example i have 610 rows , by grouping into 4 hour intervals i would only get 3 rows from that 600 and i dont what those rows to be summed up or averaged , just need the actual value at the particular time.

attached spreadsheet that has the 600 and basically i need to only return the rows in Green
LTC1minData600Rows.xlsx

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

deanmachine333

11/20/2017

ASKER

I used this scripted but changed the 4 to 240 which gave me the 4 hour intervals, thanks @Pawan

Pawan Kumar

11/20/2017

Please try full tested solution with output

;WITH CTE AS
(
	SELECT MIN([timestamp]) [timestamp], MIN(id) id, [date],LEFT(CAST(MIN(dt) AS TIME),5) HourMin,MIN([Min]) [Min] FROM 
	(
		SELECT * , CASE WHEN 
							(
								DATEDIFF(HOUR, CAST([date] as datetime),dt) < 4 
								OR 
								(DATEDIFF(HOUR, CAST([date] as datetime),dt) >= 12 AND DATEDIFF(HOUR, CAST([date] as datetime),dt) < 16) 
							)
						THEN 1 
				
							WHEN 
							(
								(DATEDIFF(HOUR, CAST([date] as datetime),dt) >= 4 AND DATEDIFF(HOUR, CAST([date] as datetime),dt) < 8) 
								OR 
								(DATEDIFF(HOUR, CAST([date] as datetime),dt) >= 16 AND DATEDIFF(HOUR, CAST([date] as datetime),dt) < 20) 
							)
						THEN 2 
						WHEN 
						(
							DATEDIFF(HOUR, CAST([date] as datetime),dt) >=8 AND  DATEDIFF(HOUR, CAST([date] as datetime),dt) < 12
							OR
							DATEDIFF(HOUR, CAST([date] as datetime),dt) >=20 AND  DATEDIFF(HOUR, CAST([date] as datetime),dt) < 24 
						)
						THEN 3
					END grp
		FROM 
		(
			SELECT [timestamp], id ,  [Date] , HourMin,[Min],[last]
			, DATEADD(day, DATEDIFF(day, 0, [Date]), HourMin + ':00') dt
			FROM 
			[dbo].[LTC1minData]
		)k
	)i
	WHERE [date] = '2017-11-20'
	GROUP BY [date],grp
)
SELECT c.*,l.Last FROM CTE c
INNER JOIN [dbo].[LTC1minData] l
ON l.Date = c.Date AND l.timestamp = c.timestamp AND l.id = c.id

Open in new window

OUTPUT

/*------------------------
OUTPUT
------------------------*/
timestamp                     id                          date           HourMin Min  Last
----------------------------- --------------------------- -------------- ------- ---- ------------
2017-11-20T08:00:48.467       BTC-LTC_2017-11-20_08:00    2017-11-20     08:00   0    0.00890995
2017-11-20T04:00:50.583       BTC-LTC_2017-11-20_04:00    2017-11-20     04:00   0    0.009015
2017-11-20T00:00:51.573       BTC-LTC_2017-11-20_00:00    2017-11-20     00:00   0    0.0089

(3 row(s) affected)

Open in new window

Welcome, glad to help !