Group by Arbitrary Number

[Leighton21]

Hi All,

I am trying to create a stored procedure that accepst a number value (in this case the number represents an hour) the results of the data should then be grouped by this value so for example I have the following
Date Value
07/01/2008 4:15 200
07/01/2008 4:35 221
07/01/2008 5:15 230
07/01/2008 6:15 100
07/01/2008 6:45 120

if the number passed to the SP is 1 (i.e. 1Hr) the result should be

Date Value
07/01/2008 4:00 421
07/01/2008 5:00 230
07/01/2008 6:00 220

(so rolled up fro the hour)

then the number could be 2 (2hr) or any number between 1 and 24 (1day) so for 2 it would be

Date Value
07/01/2008 4:00 651
07/01/2008 6:00 220

is there a group by formula that could take the number parameter and group on the hours required

cheers

 

[SQLSister]

Oh that's nasty. No there is no easy way to do this. Do you alawys start from the same place or do you start splitting up from the earliest time in your result set?

Will you cross multiple days in the query? Are you really going to try to split on every 7 hours? Or only multiples of 24. If you do, then would the bereakout be something like 0-7, 8-14, 15-21, 21-24, start over onthe next day or 0-7, 8-14, 15-21, 21-3(the next day), etc.


"NOTHING is more important in a database than integrity." ESquared

 

[PWise]

for starters

Declare @intrval int

Select @intrval =4


SELECT     SUM(Sessions) AS Session, -((DATEPART(hh, DateCreate) % @intrval)-DATEPART(hh, DateCreate))
FROM         Sessions
WHERE     (DateCreate > CONVERT(DATETIME, '2008-07-15 00:00:00', 102))
GROUP BY  -(DATEPART(hh, DateCreate) % @intrval-DATEPART(hh, DateCreate))
order by -(DATEPART(hh, DateCreate) % @intrval-DATEPART(hh, DateCreate))

 

[PWise]

Sql Sister

Your concerns are right what my code does is breakes up the day from 12:00 am in intravel of xxx hours

you might want to also group by the date

 

[Leighton21]

Thanks Guys appreciated