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 @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 @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')
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))
You can achieve that using
CTE[
^] and i would like to show you an example, but... now i'm going to sleep ;)