Time Slots Query - 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 2000 Forums

Transact-SQL (2000)

Time Slots Query

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-11-30 : 12:00:05

I'd like to figure out how much time was between the time given and the time between every half an hour time slots.

For example if i have a time of hh:mm:ss "11:30:04" this would fall into the time slot of 11:30 - 12:00 and the time used within that time slot was 4 seconds.

Another example would be a give time of 08:52:04 this would fall into the time slot of 08:30 - 09:00 and the time used within that time slot was 22 mins 4 seconds.

Now things get a bit tricky what if my 22 mins 4 seconds span over 2 time slots
Example start time was 08:20 End time was 08:42.

So the first 10 mins was in the time slot of 08:00 to 08:30
and the next 12 mins was in the time slot of 08:30 to 09:00

Or if the given time spaned over more than 2 time slots
Example Start time was 08:03:00 End time was 09:35:00

Now we have
27 mins from the time slot 08:00 - 08:30
30 mins from the time slot 08:30 - 09:00
30 mins from the time slot 09:00 - 09:30
5 mins from the time slot 09:30 - 10:00

I have a table that hold time slots they go up in 30 mins starting form 06:00 am to 18:00pm

Example
06:00
06:30
.
.
.
18:00

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-11-30 : 12:54:00

Just out of interest, how are you storing these times given that SQL Server has no 'time' data type?

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 16:46:14

[code]-- prepare test data
declare @slots table (slot int, fromtime datetime, totime datetime)

insert @slots
select number,
dateadd(second, 21600 + 1800 * number, 0),
dateadd(second, 23400 + 1800 * number, 0)
from master..spt_values
where name is null
and number between 0 and 23

declare @times table (timeid int, fromtime datetime, totime datetime)

insert @times
select 1, '08:03:04', '09:35:00' union all
select 2, '08:20', '08:42'

-- do the work
select t.timeid,
s.fromtime,
s.totime,
sum(
case when t.fromtime >= s.fromtime and t.fromtime < s.totime then datediff(second, t.fromtime, s.totime) else 0 end +
case when t.fromtime < s.fromtime and t.totime >= s.totime then datediff(second, s.fromtime, s.totime) else 0 end +
case when t.totime >= s.fromtime and t.totime < s.totime then datediff(second, s.fromtime, t.totime) else 0 end
) seconds
from @slots s
inner join @times t on t.totime >= s.fromtime and t.totime < s.totime
or t.fromtime < s.fromtime and t.totime >= s.totime
or t.fromtime >= s.fromtime and t.fromtime < s.totime
group by t.timeid,
s.fromtime,
s.totime
order by t.timeid,
s.fromtime[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-04 : 09:43:17

Please post MS ACCESS queries in the proper forum
http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-05 : 10:58:20

Thanks for your help on this peter, i plugged in my table and it works fine..

To answer "mwjdavidson" question i don't want just the time part, if i did i could copy the date time from the datatime column to a varchar column and use the substring to dirplsy just the time part..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 11:01:42

It is good to hear from you again and that the query worked out well for you.
Good luck!

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-12-14 : 06:53:45

Hi Peter,

ok here is my problem if you run this code see below you get the result which is incorrect
as the final result should be 60 and not 1860 for some reason i get 30 mins = 1800 seconds added on to
the result.

Expected result = 60
Actual result = 1860

-- the time spent between the time slot 8:30 and 9:00 was 1 min = 60 seconds

Results
FromTime ToTime Seconds
1900-01-01 08:30:00.000 1900-01-01 09:00:00.000 1860

drop table table1
create table table1
(
FromTime datetime,
ToTime datetime

)

insert into table1 (FromTime,Totime)
values('01/01/1900 08:51:00','01/01/1900 08:52:00')
-- the time spent between the time slot 8:30 and 9:00 was 1 min = 60 seconds

-- prepare test data
declare @slots table (slot int, fromtime datetime, totime datetime)

insert @slots
select number,
dateadd(second, 21600 + 1800 * number, 0),
dateadd(second, 23400 + 1800 * number, 0)
from master..spt_values
where name is null
and number between 0 and 23

declare @times table (fromtime datetime, totime datetime)

insert @times(FromTime,Totime)
select FromTime,
Totime

From table1
-- do the work
select s.fromtime,
s.totime,
sum(
case when t.fromtime >= s.fromtime and t.fromtime < s.totime then datediff(second, t.fromtime, s.totime) else 0 end +
case when t.fromtime < s.fromtime and t.totime >= s.totime then datediff(second, s.fromtime, s.totime) else 0 end +
case when t.totime >= s.fromtime and t.totime < s.totime then datediff(second, s.fromtime, t.totime) else 0 end
) seconds
from @slots s
inner join @times t on t.totime >= s.fromtime and t.totime < s.totime
or t.fromtime < s.fromtime and t.totime >= s.totime
or t.fromtime >= s.fromtime and t.fromtime < s.totime
group by
s.fromtime,
s.totime
order by
s.fromtime

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-18 : 07:18:56

Try this

SELECT		s.fromtime,
		s.totime,
		SUM(CASE
			WHEN t.FromTime < s.FromTime AND t.ToTime >= s.FromTime AND t.ToTime < s.ToTime THEN DATEDIFF(second, s.FromTime, t.ToTime)
			WHEN t.FromTime < s.FromTime AND t.ToTime > s.ToTime THEN DATEDIFF(second, s.FromTime, s.ToTime)
			WHEN t.FromTime >= s.FromTime AND t.ToTime <= s.ToTime THEN DATEDIFF(second, t.FromTime, t.ToTime)
			WHEN t.FromTime >= s.FromTime AND t.FromTime < s.ToTime AND t.ToTime >= s.ToTime THEN DATEDIFF(ss, t.FromTime, s.ToTime)
		END) Seconds
FROM		@Times t
CROSS JOIN	@Slots s
WHERE		(t.FromTime < s.FromTime AND t.ToTime >= s.FromTime AND t.ToTime < s.ToTime)
		OR (t.FromTime < s.FromTime AND t.ToTime > s.ToTime)
		OR (t.FromTime >= s.FromTime AND t.ToTime <= s.ToTime)
		OR (t.FromTime >= s.FromTime AND t.FromTime < s.ToTime AND t.ToTime >= s.ToTime)
GROUP BY	s.fromtime,
		s.totime
ORDER BY	s.FromTime

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources