SQL JOIN与缓冲时间 [英] SQL JOIN WITH A BUFFER TIME

问题描述

我有两个表，必须检查一个表中的时间是否在其他表的范围之间.

ProgStartTime给出范围的开始时间，ProgEndTime给出范围的结束，该范围位于MBA表中.正在检查MAP表中的AdvTime在MBA表中的ProgStartTime和ProgEndTime之间是否存在.

对于少于一小时的时间范围，我必须提供+或-5分钟的缓冲时间.

即ProgStarttTime为18:00&progEndTime是19:00&AdvTime是17:55/19:05，它应该与范围匹配.

如果ProgStarttTime为18:00&progEndTime是20:00&AdvTime是17:55，不应该匹配.

抱歉，内容太笨拙了.

我使用以下查询来加入缓冲时间

  SELECT DISTINCT mb.Id AS mbaid，mp.id AS mapid，mp.Channel AS频道，mp.Product，mp.ProgDate，mp.AdvTime，mb.Channel，mb.ProgStartTime，mb.ProgEndTime，convert(time，dateadd(MINUTE，datediff(MINUTE，mb.progStartTime，mb.progEndTime)，0))AS timeDiff从map22 AS mp内连接mba22 AS mb ON((mp.ProgDate = mp.ProgDateAND mp.Channel = mb.ChannelAND mp.Product = mb.Product))在哪里(mp.ProgDate = mb.ProgDateAND AdvTime> = ProgStartTimeAND(AdvTime< = ProgEndTime或ProgEndTime<ProgStartTime))或(mp.ProgDate = Dateadd(DAY，1，mb.ProgDate)AND ProgEndTime<ProgStartTimeAND AdvTime< = ProgEndTime)按mp.Id ASC排序 

解决方案

您的示例查询正在进行很多工作，因此我创建了一个简化的示例.

设置数据:

 创建表MBA(MBAID int，ProgStartTime日期时间，ProgEndTime日期时间)插入MBA选择1，'20130318 18:00:00'，'20130318 19:00:00'插入MBA选择2，'20130318 18:00:00'，'20130318 20:00:00'创建表Map(MapID int，AdvTime日期时间)插入地图选择1，'20130318 17:55:00'插入地图选择2，"20130318 18:30:00"插入地图选择3，"20130318 19:05:00"插入地图选择4，'20130318 20:05:00' 

基于此，当日期之间的时间差为一个小时或更短时，我们可以应用CASE语句为AdvTime提供更松散的匹配:

 选择*从MBA内部联接映射MBA.ProgStartTime< =当datediff(mi，MBA.ProgStartTime，MBA.ProgEndTime)< = 60时的情况然后dateadd(mi，5，Map.AdvTime)其他Map.AdvTime结尾和MBA.ProgEndTime> =当datediff(mi，MBA.ProgStartTime，MBA.ProgEndTime)< = 60时的情况然后dateadd(mi，-5，Map.AdvTime)其他Map.AdvTime结尾 

给出结果:

我们可以看到，对于持续时间为一个小时的MBA 1，我们在AdvTime值前后稍有匹配，但是对于MBA 2，我们仅根据需要匹配时间段内的AdvTime值.

.

I have tWO tables and have to check whether the time in a table lies in between range of other table.

ProgStartTime gives the start time of the range and ProgEndTime gives end of the range which lies in MBA table. Am checking whether the AdvTime in MAP table exists in between the ProgStartTime and ProgEndTime from MBA table.

For time range less than one hour i have to give a buffer of + or - 5 mins.

i.e if ProgStarttTime is 18:00 & progEndTime is 19:00 & AdvTime is 17:55/19:05 it should match the range.

Whereas if ProgStarttTime is 18:00 & progEndTime is 20:00 & AdvTime is 17:55 it shouldnt match.

Sorry for the clumsy content am in bit of a hurry.

I used the below query for joining wihtout buffer time

SELECT DISTINCT mb.Id AS mbaid,
                mp.id AS mapid,
                mp.Channel AS Channel,
                mp.Product,
                mp.ProgDate,
                mp.AdvTime,
                mb.Channel,
                mb.ProgStartTime,
                mb.ProgEndTime,
                convert(time, dateadd(MINUTE, datediff(MINUTE, mb.progStartTime, mb.progEndTime), 0)) AS timeDiff
FROM map22 AS mp
INNER JOIN mba22 AS mb ON ((mp.ProgDate = mp.ProgDate
                            AND mp.Channel=mb.Channel
                            AND mp.Product=mb.Product))
WHERE (mp.ProgDate = mb.ProgDate
       AND AdvTime >= ProgStartTime
       AND (AdvTime <= ProgEndTime
            OR ProgEndTime < ProgStartTime))
  OR (mp.ProgDate = Dateadd(DAY,1,mb.ProgDate)
      AND ProgEndTime < ProgStartTime
      AND AdvTime <= ProgEndTime)
ORDER BY mp.Id ASC

解决方案

Your sample query has a lot going on, so I've created a simplified example.

Set up the data:

create table MBA (MBAID int, ProgStartTime datetime, ProgEndTime datetime)

insert into MBA select 1, '20130318 18:00:00', '20130318 19:00:00'
insert into MBA select 2, '20130318 18:00:00', '20130318 20:00:00'

create table Map (MapID int, AdvTime datetime)

insert into Map select 1, '20130318 17:55:00'
insert into Map select 2, '20130318 18:30:00'
insert into Map select 3, '20130318 19:05:00'
insert into Map select 4, '20130318 20:05:00'

Based on this, we can apply a CASE statement to give AdvTime more loose matching when the difference between the dates is an hour or less:

select *
from MBA
  inner join Map on
    MBA.ProgStartTime <=
      case when datediff(mi, MBA.ProgStartTime, MBA.ProgEndTime) <= 60
        then dateadd(mi, 5, Map.AdvTime)
        else Map.AdvTime
        end
    and MBA.ProgEndTime >=
      case when datediff(mi, MBA.ProgStartTime, MBA.ProgEndTime) <= 60
        then dateadd(mi, -5, Map.AdvTime)
        else Map.AdvTime
        end

Gives results:

We can see that for MBA 1, which has an hour duration, we are matching AdvTime values slightly before and after, but for MBA 2, only matching those within the time period as required.

SQL Fiddle with demo.

Edit after comment:

Added another example for the values in the comments, with following data:

create table MBA (MBAID int, ProgStartTime datetime, ProgEndTime datetime)

insert into MBA select 1, '20130318 21:00:00', '20130318 22:00:00'

create table Map (MapID int, AdvTime datetime)

insert into Map select 1, '20130318 20:55:00'
insert into Map select 2, '20130318 22:05:00'

The original query matches both of the above rows as expected.

SQL Fiddle with demo.

Edit after comment:

Tested with more data:

create table MBA (MBAID int, ProgStartTime datetime, ProgEndTime datetime)

insert into MBA select 1, '20130318 23:00:00', '20130319 02:00:00'

create table Map (MapID int, AdvTime datetime)

insert into Map select 1, '20130319 00:30:00'

Still matching as expected.

SQL Fiddle with demo.

Final edit after comment?

OK, now we know a bit more about the schema we can make one final query. Set up the data:

create table MBA (MBAID int, ProgStartTime datetime, ProgEndTime datetime)

insert into MBA select 1, '18:00:00', '19:00:00'
insert into MBA select 2, '18:00:00', '20:00:00'
insert into MBA select 3, '21:00:00', '22:00:00'
insert into MBA select 4, '23:30:00', '02:00:00'
insert into MBA select 5, '23:30:00', '00:30:00'


create table Map (MapID int, AdvTime datetime)

insert into Map select 1, '17:55:00'
insert into Map select 2, '18:30:00'
insert into Map select 3, '19:05:00'
insert into Map select 4, '20:05:00'
insert into Map select 5, '20:55:00'
insert into Map select 6, '22:05:00'
insert into Map select 7, '23:25:00'
insert into Map select 8, '23:30:00'
insert into Map select 9, '00:30:00'
insert into Map select 10, '00:35:00'

Use the following query:

select *
from MBA
  inner join Map on
    (MBA.ProgStartTime < MBA.ProgEndTime
      and MBA.ProgStartTime <=
        case when datediff(mi, MBA.ProgStartTime, MBA.ProgEndTime) <= 60
          then dateadd(mi, 5, Map.AdvTime)
          else Map.AdvTime
          end
      and MBA.ProgEndTime >=
        case when datediff(mi, MBA.ProgStartTime, MBA.ProgEndTime) <= 60
          then dateadd(mi, -5, Map.AdvTime)
          else Map.AdvTime
          end) or
    (MBA.ProgStartTime > MBA.ProgEndTime
      and (MBA.ProgStartTime <=
        case when 1440 - datediff(mi, MBA.ProgEndTime, MBA.ProgStartTime) <= 60
          then dateadd(mi, 5, Map.AdvTime)
          else Map.AdvTime
          end
      or MBA.ProgEndTime >=
        case when 1440 - datediff(mi, MBA.ProgEndTime, MBA.ProgStartTime) <= 60
          then dateadd(mi, -5, Map.AdvTime)
          else Map.AdvTime
          end))

We expect the following rows to be matched:

MBA  Matched Maps
1    1,2,3
2    2,3
3    5,6
4    8,9,10
5    7,8,9,10

Results:

SQL Fiddle with demo.

这篇关于SQL JOIN与缓冲时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！