Group by 15 minutes not Hour

Question

Group by 15 minutes not Hour

TRACEY-320982

SSChampion

Points: 13653
More actions
July 17, 2009 at 3:25 pm

#133968

SELECTDATEPART(HH,DAYLIGHT_TIME) AS HOUR,
MAX(myvalue)
How to sort this by every 15 minutes. Or Every 10 minutes.
7/14/09 8:02 AM44.81
7/14/09 8:15 AM152.03
7/14/09 8:15 AM17.36
7/14/09 8:15 AM13.36
7/14/09 8:15 AM2.12
7/14/09 8:30 AM169.15
7/14/09 8:30 AM14.92
7/14/09 8:45 AM171.00
7/14/09 8:45 AM14.00
7/14/09 8:45 AM0.52
7/14/09 8:55 AM814.57
7/14/09 8:55 AM21.32
7/14/09 9:00 AM388.31
7/14/09 9:00 AM19.72
7/14/09 9:00 AM0.52
7/14/09 9:15 AM152.09
7/14/09 9:15 AM18.28
7/14/09 9:15 AM15.28
7/14/09 9:30 AM152.23
7/14/09 9:30 AM17.88
7/14/09 9:30 AM16.28
7/14/09 9:45 AM174.85
7/14/09 9:45 AM15.20
7/14/09 9:45 AM0.52
7/14/09 9:55 AM829.07
7/14/09 9:55 AM25.36

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

First, a suggestion especially since you've got 613 points and you should really know better by now.. Read the first link in my signature line below for how to post data in a readily consumable format so that folks will actually try to help you. I'm doing it for you this once. 😉

Here's what your data should have looked like when you posted it... like I said, easily consumable...

CREATE TABLE #SomeTable

(SomeDateTime DATETIME, SomeValue DECIMAL(9,2))

INSERT INTO #SomeTable

(SomeDateTime, SomeValue)

SELECT '7/14/09 8:15 AM',152.03 UNION ALL

SELECT '7/14/09 8:15 AM',17.36 UNION ALL

SELECT '7/14/09 8:15 AM',13.36 UNION ALL

SELECT '7/14/09 8:15 AM',2.12 UNION ALL

SELECT '7/14/09 8:30 AM',169.15 UNION ALL

SELECT '7/14/09 8:30 AM',14.92 UNION ALL

SELECT '7/14/09 8:45 AM',171.00 UNION ALL

SELECT '7/14/09 8:45 AM',14.00 UNION ALL

SELECT '7/14/09 8:45 AM',0.52 UNION ALL

SELECT '7/14/09 8:55 AM',814.57 UNION ALL

SELECT '7/14/09 8:55 AM',21.32 UNION ALL

SELECT '7/14/09 9:00 AM',388.31 UNION ALL

SELECT '7/14/09 9:00 AM',19.72 UNION ALL

SELECT '7/14/09 9:00 AM',0.52 UNION ALL

SELECT '7/14/09 9:15 AM',152.09 UNION ALL

SELECT '7/14/09 9:15 AM',18.28 UNION ALL

SELECT '7/14/09 9:15 AM',15.28 UNION ALL

SELECT '7/14/09 9:30 AM',152.23 UNION ALL

SELECT '7/14/09 9:30 AM',17.88 UNION ALL

SELECT '7/14/09 9:30 AM',16.28 UNION ALL

SELECT '7/14/09 9:45 AM',174.85 UNION ALL

SELECT '7/14/09 9:45 AM',15.20 UNION ALL

SELECT '7/14/09 9:45 AM',0.52 UNION ALL

SELECT '7/14/09 9:55 AM',829.07 UNION ALL

SELECT '7/14/09 9:55 AM',25.36

Using that test data, here's one possible answer that's been tested...

DECLARE @MinutesInPeriod INT

SELECT @MinutesInPeriod = 10

SELECT DATEADD(mi,DATEDIFF(mi,'20090714',SomeDateTime) / @MinutesInPeriod * @MinutesInPeriod, '20090714') AS Period,

MAX(SomeValue) AS MaxSomeValue

FROM #SomeTable

GROUP BY DATEADD(mi,DATEDIFF(mi,'20090714',SomeDateTime) / @MinutesInPeriod * @MinutesInPeriod, '20090714')

That brings us to part 2 of how to post a question. What is it that you actually want? For example, the solution above doesn't return a value for time periods that have no entries. Would that be important to you? You really need to be just a wee bit more detailed in describing your problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 2

I take a look, i only wanted the command not the entire program 🙂

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

TRACEY (7/17/2009)
I take a look, i only wanted the command not the entire program 🙂

Heh... understood. Would you have been able to figure it out if I were to have gone with my first inclination and posted just the simple word hint to the command of "Integer Division of Minutes With A Group By"?

And, you're welcome.;-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TRACEY-320982 SSChampion Points: 13653 More actions · Answer 4

No...i needed a nudge to help me get my head around it. Cheers.........

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

TRACEY (7/17/2009)
How to sort this by every 15 minutes. Or Every 10 minutes.

I'll have to remember that's how you ask for a "nudge" in the future.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)