加入查询以匹配时间范围

本文介绍了加入查询以匹配时间范围的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两张桌子。 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 ;)