quote:

---

Originally posted by hisouka

THis is my detailed question...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1962500&SiteID=1

---

-- Prepare sample data
DECLARE	@Car1 TABLE (aNumber INT, bNumber INT, CallDate SMALLDATETIME, CallTime SMALLDATETIME, Duration INT)

INSERT	@Car1
SELECT	330, 44932, '20060101', '07:30',5 UNION ALL
SELECT	330, 44932, '20060101', '12:46', 125 UNION ALL
SELECT	330, 44932, '20060101', '23:12', 25

DECLARE	@Car2 TABLE (aNumber INT, bNumber INT, CallDate SMALLDATETIME, CallTime SMALLDATETIME, Duration INT)

INSERT	@Car2
SELECT	330, 44932, '20060101', '07:15', 5 UNION ALL
SELECT	330, 44932, '20060101', '12:36', 122 UNION ALL
SELECT	330, 44932, '20060101', '23:01', 28

-- Show the expect output
SELECT		c1.aNumber,
		c1.bNumber,
		c1.CallDate,
		c1.CallTime AS c1time,
		c1.Duration AS c1duration,
		c2.CallTime AS c2time,
		c2.Duration AS c2duration
FROM		@Car1 AS c1 
INNER JOIN	@Car2 AS c2 ON c2.aNumber = c1.aNumber
			AND c2.bNumber = c1.bNumber
			AND c2.CallDate = c1.CallDate
			AND c2.CallTime = (SELECT TOP 1 c2.CallTime FROM @Car2 AS c2 WHERE c2.aNumber = c1.aNumber AND c2.bNumber = c1.bNumber AND c2.CallDate = c1.CallDate ORDER BY ABS(DATEDIFF(MINUTE, c1.CallTime, c2.CallTime)))