Grouping by a declared "Time" range

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

General SQL Server Forums

New to SQL Server Programming

Grouping by a declared "Time" range

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-17 : 19:42:55

I've been trying to figure out how I might be able to make a particular query that gets asked repeatedly easier to set up.

In our business, we deal with "numbers of trips" that happen over a time range. I consistently get asked for how many trips occurred between time X and time y.

Normally, I will just put something like this together:


declare @Trips table (id int, BoardingTime time)
insert into @Trips (id, BoardingTime)
values
(1, '06:05:00.000'),
(2, '06:15:00.000'),
(3, '06:35:00.000'),
(4, '06:46:00.000'),
(5, '07:11:00.000')

select	SUM(case when t.BoardingTime>='06:15:00.000' and t.BoardingTime<'06:45:00.000' then 1 else 0 end) as '6:15 - 6:45',
		SUM(case when t.BoardingTime>='06:45:00.000' and t.BoardingTime<'07:15:00.000' then 1 else 0 end) as '6:45 - 7:15'
from @Trips t

This works great. However, the time ranges are almost never standardized and I generally have to pull time ranges for approximately an 18-20 hour period. This is tedious and the chance of error while keying in these times can be a bit nerve wracking.

I've been trying to use some if then code blocks to conditionally pull the times into groups, but the ranges can sometimes be 20 minutes or sometimes up to 1.75 hours.

I was hoping to come up with something where I can declare a "from minute" and "to minute" value so that I could automatically group the data based on the from/start minute times that I set. Nothing so far has panned out due to the variability of the problem (and my experience of course) but was hoping maybe someone had an idea or a reference article they might point me to.

This following code probably can't be fixed, but these are some things I've tried:


Limited success with this...only really works if the time range is less than 1 hour and a couple other things line up as well:

declare	 @TimeRanges table (FromTime time, ToTime time)
declare  @RunTime int 
		,@Time time
		,@From int
		,@To   int


select   @RunTime=30
		,@Time='00:00:00.000'
		,@From=15
		,@To=45

begin
if DATEDIFF(hh, DATEADD(mi, @From, @Time), DATEADD(mi, @RunTime, DATEADD(mi, @From, @Time)))=0

insert into @TimeRanges (FromTime, ToTime)
(
select  DATEADD(mi, @From, CONVERT(time, DATEADD(hh, number, 0))) as FromTime,
		DATEADD(mi, @To,   CONVERT(time, DATEADD(hh, number, 0))) as ToTime
from master..spt_values
where type='p' and number between 0 and 23)

else 

if DATEDIFF(hh, DATEADD(mi, @From, @Time), DATEADD(mi, @RunTime, DATEADD(mi, @From, @Time)))>0
insert into @TimeRanges (FromTime, ToTime)
(
select  DATEADD(mi, @From, CONVERT(time, DATEADD(hh, number, 0))) as FromTime,
		DATEADD(mi, @To,   CONVERT(time, DATEADD(hh, number+1, 0))) as ToTime
from master..spt_values
where type='p' and number between 0 and 23)

end

begin
if DATEDIFF(hh, DATEADD(mi, @To, @Time), DATEADD(mi, @RunTime, DATEADD(mi, @To, @Time)))=0

insert into @TimeRanges (FromTime, ToTime)
(
select  DATEADD(mi, @To,	 CONVERT(time, DATEADD(hh, number, 0))) as FromTime,
		DATEADD(mi, @From,   CONVERT(time, DATEADD(hh, number, 0))) as ToTime
from master..spt_values
where type='p' and number between 0 and 23)
else

if DATEDIFF(hh, DATEADD(mi, @To, @Time), DATEADD(mi, @RunTime, DATEADD(mi, @To, @Time)))>0

insert into @TimeRanges (FromTime, ToTime)
(
select  DATEADD(mi, @To,	 CONVERT(time, DATEADD(hh, number, 0))) as FromTime,
		DATEADD(mi, @From,   CONVERT(time, DATEADD(hh, number+1, 0))) as ToTime
from master..spt_values
where type='p' and number between 0 and 23)

end

select *
from @TimeRanges
order by 1

Results:



FromTime         ToTime
---------------- ----------------
00:15:00.0000000 00:45:00.0000000
00:45:00.0000000 01:15:00.0000000
01:15:00.0000000 01:45:00.0000000
01:45:00.0000000 02:15:00.0000000
02:15:00.0000000 02:45:00.0000000
02:45:00.0000000 03:15:00.0000000
03:15:00.0000000 03:45:00.0000000
03:45:00.0000000 04:15:00.0000000
04:15:00.0000000 04:45:00.0000000
04:45:00.0000000 05:15:00.0000000
05:15:00.0000000 05:45:00.0000000
05:45:00.0000000 06:15:00.0000000
06:15:00.0000000 06:45:00.0000000
06:45:00.0000000 07:15:00.0000000
07:15:00.0000000 07:45:00.0000000
07:45:00.0000000 08:15:00.0000000
08:15:00.0000000 08:45:00.0000000
08:45:00.0000000 09:15:00.0000000
09:15:00.0000000 09:45:00.0000000
09:45:00.0000000 10:15:00.0000000
10:15:00.0000000 10:45:00.0000000
10:45:00.0000000 11:15:00.0000000
11:15:00.0000000 11:45:00.0000000
11:45:00.0000000 12:15:00.0000000
12:15:00.0000000 12:45:00.0000000
12:45:00.0000000 13:15:00.0000000
13:15:00.0000000 13:45:00.0000000
13:45:00.0000000 14:15:00.0000000
14:15:00.0000000 14:45:00.0000000
14:45:00.0000000 15:15:00.0000000
15:15:00.0000000 15:45:00.0000000
15:45:00.0000000 16:15:00.0000000
16:15:00.0000000 16:45:00.0000000
16:45:00.0000000 17:15:00.0000000
17:15:00.0000000 17:45:00.0000000
17:45:00.0000000 18:15:00.0000000
18:15:00.0000000 18:45:00.0000000
18:45:00.0000000 19:15:00.0000000
19:15:00.0000000 19:45:00.0000000
19:45:00.0000000 20:15:00.0000000
20:15:00.0000000 20:45:00.0000000
20:45:00.0000000 21:15:00.0000000
21:15:00.0000000 21:45:00.0000000
21:45:00.0000000 22:15:00.0000000
22:15:00.0000000 22:45:00.0000000
22:45:00.0000000 23:15:00.0000000
23:15:00.0000000 23:45:00.0000000
23:45:00.0000000 00:15:00.0000000

The idea above was to dynamically create a table that would be joined to my data set so that I could set up the groups based on the "TimeRange" declared table.

Thanks

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-03-18 : 06:46:10

hi,

do you have a desired output that you want?
please post it and i'm sure we can solve this issue.

thanks

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-18 : 10:57:17

Slimt,

Thanks for your reply. The last result set is perfect for the time range that I posted. However, say I declare the variables as such:


select   @RunTime=90
	,@Time='00:00:00.000'
	,@From=15
	,@To=30

What I'd actually want to see would be this:


FromTime         ToTime
00:15	01:30
01:30	02:45
02:45	04:00
04:00	05:15
05:15	06:30
06:30	07:45
07:45	09:00
09:00	10:15
10:15	11:30
11:30	12:45
12:45	14:00
14:00	15:15
15:15	16:30
16:30	17:45
17:45	19:00
19:00	20:15
20:15	21:30
21:30	22:45
22:45	00:00

There are several other time range examples that I know won't work with my code such as if I have shorter ranges within a 1 hour period. For example, if I set the @runtime=20 I wanted to see 3 time ranges per hour (say from 00:10 to 00:30 then from 00:30 to 00:50 then from 00:50 to 01:10, the code won't support that. I'm not expecting to fix what I wrote, it was more just an attempt to figure out the problem. I'm guessing that I need to do something vastly different.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 13:08:06

sounds like this?

http://visakhm.blogspot.com/2010/02/aggregating-data-over-time-slots.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-18 : 13:20:37

Vis...as usual, you're a god! That's pretty much exactly what I was looking for!!! Thank you so much. Some of it is still a bit over my head, but I think I can trudge through it.

Thanks again!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 13:24:01

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-18 : 13:48:10

Vis,

One quick question...Did you use the random number in the function to return sample results to represent a sum quantity? Or is that inherently needed in the function? Just trying to make sure I completely understand it. The slotwidth and other aspects to the function make sense to me.

EDIT: Duh...nevermind...yes, I got it now :) It's just sample data.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 14:24:30

quote:
Originally posted by flamblaster

Vis,

One quick question...Did you use the random number in the function to return sample results to represent a sum quantity? Or is that inherently needed in the function? Just trying to make sure I completely understand it. The slotwidth and other aspects to the function make sense to me.

EDIT: Duh...nevermind...yes, I got it now :) It's just sample data.

its just to create the dataset and has nothing to do with function

Yep..anyways you got it right.....so fine

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-18 : 15:36:14

Sorry to be a pest...I'm working through it and the one adjustment I need to make is to start the time sequence based on user input. I'm thinking I need to add one variable to the function such as @UserStart Time

The idea being:

Select @UserStart='07:10:00', @SlotWidth=20

When calling the function, instead of starting at '07:00:00' and running for 20 minute increments, it would run from '07:10:00' for 20 minute increments. I'm trying to do a dateadd between the @UserStart and LogTime to produce those results, but if you have an idea, that would help. I think I might muck this up royally :p

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 16:30:19

use this modified function instead


IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME ='GetSlotTotals' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE='FUNCTION')
DROP FUNCTION dbo.GetSlotTotals123
GO
CREATE FUNCTION dbo.GetSlotTotals123
(
@InputTable LogTable READONLY,
@userstart datetime,
@SlotWidth int
)
RETURNS @RESULTS Table
(
ID int IDENTITY(1,1),
SlotStart datetime,
SlotEnd datetime,
ValSum int
)
AS
BEGIN

INSERT INTO @RESULTS (SlotStart,SlotEnd,ValSum)
SELECT DATEADD(mi,DATEDIFF(mi,0,LogTime)/@SlotWidth*@SlotWidth,0) As Start , DATEADD(mi,(DATEDIFF(mi,0,LogTime)/@SlotWidth+1)*@SlotWidth-1,0) AS [End], SUM(Value)
FROM @InputTable
WHERE LogTime> @userstart
GROUP BY DATEADD(mi,DATEDIFF(mi,0,LogTime)/@SlotWidth*@SlotWidth,0), DATEADD(mi,(DATEDIFF(mi,0,LogTime)/@SlotWidth+1)*@SlotWidth-1,0)

RETURN
END

and call  it like for example

SELECT SlotStart,SlotEnd,ValSum FROM dbo.GetSlotTotals(@MyTable,'2012-03-19 01:38',2) ORDER BY SlotStart


output
------------------------------------------------------------------------
ID	SlotStart	         SlotEnd	                ValSum
------------------------------------------------------------------------
1	2012-03-19 01:38:00.000	 2012-03-19 01:39:00.000	85
2	2012-03-19 01:40:00.000	 2012-03-19 01:41:00.000	81
3	2012-03-19 01:42:00.000	 2012-03-19 01:43:00.000	77

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-18 : 19:57:30

That makes total sense! Thank you. I initially tried to modify your code in "insert" portion of the function like you had...but instead of LogTime> @userstarttime, I was putting MyActualDataTime>@UserStart...so it was having no affect on the log time.

Thanks for sticking through it...this will be great!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 00:17:58

welcome...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources