加入查询以匹配时间范围 [英] Join query to match time range

问题描述

我有两张桌子。



MAP

I have two tables.

MAP

Product    Channel  ProgDate   AdvTime 
Air    Pix  30-04-2011  20:57:16
Air    Pix  30-05-2011  00:55:08

MBA

MBA

Product    Channel Date   ProgStartTime ProgEndTime
Air    Pix  30-04-2011  23:00:00   02:00:00
Air    Pix  30-04-2011  21:00:00   22:00:00

我必须检查MAP中的AdvtTime是否介于StartTime和amp之间; MBA表中的结束时间。



但是当持续时间（ProgStartTime - ProgEndTime）为1小时时，我需要一个+或 - 5分钟的缓冲区。



我需要00:05:08以匹配23:00:00至02:00:00，以及20:57:16以匹配21:00:00至22:00:00 。



我使用以下查询加入

I have to check whether AdvtTime in MAP is between the StartTime & EndTime in MBA table.

But when the duration (ProgStartTime - ProgEndTime) is one hour I need a buffer of + or - 5 mins.

I need 00:05:08 to match 23:00:00 to 02:00:00, and 20:57:16 to match 21:00:00 to 22:00:00.

I used the below query to join

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 

推荐答案

下面的例子是MS SQL Server是2005，MS SQL Server最新版本中提供的许多功能都不适用于2005版本，​​例如：time，date，datetimeofset等。



我准备了示例数据：

Below example is for MS SQL Server is 2005, so many features available in newest version of MS SQL Server, are not available for 2005 version, like: time, date, datetimeofset, etc.

I prepared example data:

--declare variable (type of table) for MAP table
DECLARE @map TABLE(Product NVARCHAR(30), Channel NVARCHAR(30), ProgDate DATETIME, AdvTime NVARCHAR(8))
INSERT INTO @map (Product, Channel, ProgDate, AdvTime )
VALUES('Air' , 'Pix' , '2011-04-30' , '20:57:16')
INSERT INTO @map (Product, Channel, ProgDate, AdvTime )
VALUES('Air' , 'Pix' , '2011-04-30' , '00:55:08')

--declare variable (type of table) for MBA table
DECLARE @mba TABLE(Product NVARCHAR(30), Channel NVARCHAR(30), Date DATETIME, ProgStartTime NVARCHAR(8), ProgEndTime NVARCHAR(8))
INSERT INTO @mba (Product, Channel, Date, ProgStartTime, ProgEndTime)
VALUES ('Air' , 'Pix' , '2011-04-30' , '23:00:00' , '02:00:00')
INSERT INTO @mba (Product, Channel, Date, ProgStartTime, ProgEndTime)
VALUES ('Air' , 'Pix' , '2011-05-30' , '21:00:00' , '22:00:00')

--get values:
SELECT FT.*
FROM (
    SELECT Product, Channel, AdvTime, StartTime, 'EndTime' = CASE WHEN EndTime < StartTime THEN DATEADD(d,1,EndTime) ELSE EndTime END
    FROM (
        SELECT t1.Product, t1.Channel, CONVERT(DATETIME, t1.ProgDate + ' ' +  t1.AdvTime) AS AdvTime, CONVERT(DATETIME, t2.Date + ' ' + t2.ProgStartTime) AS StartTime, CONVERT(DATETIME, t2.Date + ' ' + t2.ProgEndTime) AS EndTime
        FROM @map AS t1 LEFT JOIN @mba AS t2 ON t1.Product = t2.Product AND t1.Channel = t2.Channel AND t1.ProgDate = t2.Date
        ) AS DT
    ) AS FT
WHERE (CAST(FT.Advtime AS INT) >= CAST(FT.StartTime AS INT) AND CAST(FT.Advtime AS INT) <= CAST(FT.EndTime AS INT))

你可以用 CTE [ ^ ]我想给你看一个例子，但是......现在我要睡觉了;）

You can achieve that using CTE[^] and i would like to show you an example, but... now i''m going to sleep ;)

这篇关于加入查询以匹配时间范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！