在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屋！