grouping by variable timeslot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

grouping by variable timeslot

Author

Topic

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-17 : 06:14:08

maybe i'm over-thinking this one, but it's giving me a headache

I need to aggregate some data into 'timeslots'. the length of the timeslots will actually be variable / user-definable, but i'm not bothered about that for now. sufficed to say it will be an integer value relating to the number of minutes in the 'timeslot', with a minimum of 30. so I'm just testing with 30 for now

test data here...


declare @t table (datecol datetime, col varchar(50),num int)
insert into @t
            select '20080101 00:01:00.000','test 1',1
union all   select '20080101 00:20:00.000','test 1',2
union all   select '20080101 00:31:00.000','test 1',1
union all   select '20080101 00:40:00.000','test 1',4
union all   select '20080101 00:50:00.000','test 1',6
union all   select '20080101 01:10:00.000','test 1',1
union all   select '20080101 01:28:00.000','test 1',1
union all   select '20080101 01:35:00.000','test 1',2

select * from @t
 

based on above i need the result to be like...
[code]
/*
startDT			endDT			col		sum_num
'20080101 00:00:00.000' '20080101 00:30:00.000'	'test 1'	3
'20080101 00:30:00.000' '20080101 01:00:00.000'	'test 1'	11
'20080101 01:00:00.000' '20080101 01:30:00.000'	'test 1'	2
'20080101 01:30:00.000' '20080101 02:00:00.000'	'test 1'	2
*/


so... I figured I could maybe use a CTE to get the startDT and endDT i need for the output then join back to my test data to do the grouping. make sense?

only, all we've got in the kitchen is decaff coffee, and i've got no chocolate left in my drawer, and i can't get it to work, and i can't help thinking i'm over complicating things any way 

this is what i'd started writing for the CTE, though it doesn't work...

[code]
;WITH timeslots (startTS, endTS, [Level])
AS
(
	select startTS,endTS,[level]
	from
	(select '20000101 00:00:00.000' as startTS, dateadd(mi,30,'20000101 00:00:00.000') as endTS, [level] = 1) t1
	union all
	select dateadd(mi,30,startTS) as startTS, dateadd(mi,30,endTS) as endTS, [level] = [level] + 1
	from
	(select '20000101 00:00:00.000' as startTS, dateadd(mi,30,'20000101 00:00:00.000') as endTS, [level] = 1) t1
	join timeslots ts on ts.endTS = t1.startTS
)

any help appreciated guys

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 06:45:18

What happens if you decide to use a slot interval that is not even divisable with 60?
For example, say you use 11 minutes; from when do the 11 minute intervals start?

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-17 : 06:48:29

i'm planning to enforce that's always rounded to 30 mins. i.e. 30 / 60 / 90 etc..

Em

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:03:24

Or any other number even divisable with 1440 (full day, 24 hours)?

SELECT	Number AS MinuteInterval
FROM	master..spt_values
WHERE	Type = 'p'
	AND Number BETWEEN 1 AND 1440
	AND 1440 % Number = 0

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-17 : 07:15:00

eek... just got the CTE 'partially' working, but hit the maximum recursion of 100

doh!

back to square 1 then?

Peter - yes, i guess i could make it anything that 'fits' into a day, thanks for the pointer. but I don't really want to give users that much flexibility

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:18:43

[code]CREATE FUNCTION dbo.fnGetIntervals
(
@BaseDate DATETIME,
@TotalDays SMALLINT,
@MinuteInterval SMALLINT
)
RETURNS TABLE
AS
RETURN (
SELECT DATEADD(MINUTE, m.Number * @MinuteInterval, DATEADD(DAY, d.Number, @BaseDate)) AS startDT,
DATEADD(MINUTE, m.Number * @MinuteInterval + @MinuteInterval, DATEADD(DAY, d.Number, @BaseDate)) AS endDT
FROM master..spt_values AS d
INNER JOIN master..spt_values AS m ON m.Type = 'p'
WHERE d.Type = 'p'
AND d.Number < @TotalDays
AND m.Number < 1440 / @MinuteInterval
)
GO

declare @t table (datecol datetime, col varchar(50),num int)
insert into @t
select '20080101 00:01:00.000','test 1',1
union all select '20080101 00:20:00.000','test 1',2
union all select '20080101 00:31:00.000','test 1',1
union all select '20080101 00:40:00.000','test 1',4
union all select '20080101 00:50:00.000','test 1',6
union all select '20080101 01:10:00.000','test 1',1
union all select '20080101 01:28:00.000','test 1',1
union all select '20080101 01:35:00.000','test 1',2

SELECT y.startdt,
y.enddt,
t.col,
sum(num) AS snum_num
FROM dbo.fnGetIntervals('20080101', 1, 30) AS y
LEFT JOIN @t AS t ON t.datecol >= y.startdt
and t.datecol < y.enddt
GROUP BY y.startdt,
y.enddt,
t.col
ORDER BY y.startdt[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-17 : 07:20:55