| Author |
 Topic  |
|
|
rookie_sql
Constraint Violating Yak Guru
Ireland
441 Posts |
 Posted - 11/30/2006 : 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
United Kingdom
735 Posts |
Posted - 11/30/2006 : 12:54:00
|
Just out of interest, how are you storing these times given that SQL Server has no 'time' data type?
Mark |
 |
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 11/30/2006 : 16:46:14
|
-- 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
Peter Larsson
Helsingborg, Sweden |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
|
|
rookie_sql
Constraint Violating Yak Guru
Ireland
441 Posts |
Posted - 12/05/2006 : 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..
|
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/05/2006 : 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
Ireland
441 Posts |
Posted - 12/14/2006 : 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
|
Edited by - rookie_sql on 12/15/2006 09:36:23 |
|
|
|
Peso
Patron Saint of Lost Yaks
Sweden
27383 Posts |
Posted - 12/18/2006 : 07:18:56
|
Try thisSELECT 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 |
|
|
| |
Topic |
|
|
|
Time Slots Query
