在t-sql间隔时间+持续时间间隔 [英] Splitting time + duration into intervals in t-sql
问题描述
我有一个表,包括事件的开始时间和相关的持续时间。我需要能够将事件持续时间分为30分钟。所以例如,如果事件从10:45:00开始,持续时间为00:17:00,则返回的集合应该分配15分钟到10:30:00间隔和00:02:00分钟到11:00 :00间隔。
我确定我可以弄清楚一个笨拙的方法,但是会有点简单一些。这一定是我经常想到的,但谷歌今天没有帮助。
谢谢,
Steve
您可以创建一个只有时间(超过24小时)的查找表,并加入该表。您需要将日期修改为查找中使用的日期。然后在上下间隔执行一个日期,以确定其持续时间。每个中间间隔将是30分钟。
创建表#interval_lookup(
from_date datetime,
to_date datetime
)
声明@time datetime
set @time = '00:00:00'
while @time< '1900年1月2日'
begin
insert into #interval_lookup values(@time,dateadd(minute,30,@time))
set @time = dateadd(minute,30,@time)
end
declare @search_from datetime
declare @search_to datetime
set @search_from = '10:45:00'
set @ search_to = dateadd(分钟,17,@search_from)
选择
from_date作为间隔,
case
当from_date< = @search_from和
@搜索_从< to_date和
from_date< = @search_to和
@search_to< to_date
then datediff(分钟,@search_from,@search_to)
当from_date< = @search_from和
@search_from< to_date
then datediff(分钟,@search_from,to_date)
当from_date< = @search_to和
@search_to< to_date然后
datediff(分钟,from_date,@search_to)
其他30
结束为
从
#interval_lookup
其中
to_date> @search_from
和from_date< = @search_to
Does anyone know of a simple method for solving this?
I have a table which consists of start times for events and the associated durations. I need to be able to split the event durations into thirty minute intervals. So for example if an event starts at 10:45:00 and the duration is 00:17:00 then the returned set should allocate 15 minutes to the 10:30:00 interval and 00:02:00 minutes to the 11:00:00 interval.
I'm sure I can figure out a clumsy approach but would like something a little simpler. This must come up quite often I'd imagine but Google is being unhelpful today.
Thanks,
Steve
You could create a lookup table with just the times (over 24 hours), and join to that table. You would need to rebase the date to that used in the lookup. Then perform a datediff on the upper and lower intervals to work out their durations. Each middle interval would be 30 minutes.
create table #interval_lookup (
from_date datetime,
to_date datetime
)
declare @time datetime
set @time = '00:00:00'
while @time < '2 Jan 1900'
begin
insert into #interval_lookup values (@time, dateadd(minute, 30, @time))
set @time = dateadd(minute, 30, @time)
end
declare @search_from datetime
declare @search_to datetime
set @search_from = '10:45:00'
set @search_to = dateadd(minute, 17, @search_from)
select
from_date as interval,
case
when from_date <= @search_from and
@search_from < to_date and
from_date <= @search_to and
@search_to < to_date
then datediff(minute, @search_from, @search_to)
when from_date <= @search_from and
@search_from < to_date
then datediff(minute, @search_from, to_date)
when from_date <= @search_to and
@search_to < to_date then
datediff(minute, from_date, @search_to)
else 30
end as duration
from
#interval_lookup
where
to_date > @search_from
and from_date <= @search_to
这篇关于在t-sql间隔时间+持续时间间隔的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!