Closest matching recod (datediff??)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Closest matching recod (datediff??)

Author

Topic

harleyquin
Starting Member

2 Posts

Posted - 2008-07-15 : 05:22:10

Hello all, first post here, and I'm not 100% sure if this is the correct forum buit this was arecomended as a good info forum so dont be too harsh on me, I'll probably put too much blerb on but want to be give as much info as possible.

I have two tables - one is a master booking, the other is when a customer called

Table A
UserID: 1 BookedDate: 01/01/2008 10:00:00
UserID: 2 BookedDate: 02/01/2008 11:15:00

Table B
UserId: 1 CallTime: 01/01/2008 09:59:10
UserId: 1 CallTime: 01/01/2008 10:01:00
UserId: 1 CallTime: 01/01/2008 10:10:00
UserId: 2 CallTime: 01/01/2008 09:50:10
UserId: 2 CallTime: 02/01/2008 10:50:10
UserId: 2 CallTime: 02/01/2008 11:15:10

I know if I do a datediff in seconds I can add find the value in say seconds, I dont think I can use a MIN as I can have positive and negative numbers, where the positive number may be my closest match so to speak

from the example above I would expect the return for "closest match to BookedDate"

ID
1 01/01/2008 09:59:10
2 02/01/2008 11:15:10

I'm floating with the idea of Select top 1 but I'm just not sure how to eloquently code this to return the thrid table with the closest match

anyway thanks for any info provided and if I'm in the wrong place sorry for the post

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 05:35:04

[code]DECLARE @TableA TABLE (UserID INT, BookedDate DATETIME)

INSERT @TableA
SELECT 1, '01/01/2008 10:00:00' UNION ALL
SELECT 2, '02/01/2008 11:15:00'

DECLARE @TableB TABLE (UserID INT, BookedDate DATETIME)

INSERT @TableB
SELECT 1, '01/01/2008 09:59:10' UNION ALL
SELECT 1, '01/01/2008 10:01:00' UNION ALL
SELECT 1, '01/01/2008 10:10:00' UNION ALL
SELECT 2, '01/01/2008 09:50:10' UNION ALL
SELECT 2, '02/01/2008 10:50:10' UNION ALL
SELECT 2, '02/01/2008 11:15:10'

;WITH Yak (UserID, BookedDateA, BookedDateB, RecID)
AS (
SELECT a.UserID,
a.BookedDate AS BookedDateA,
b.BookedDate AS BookedDateB,
ROW_NUMBER() OVER (PARTITION BY a.UserID ORDER BY ABS(DATEDIFF(SECOND, a.BookedDate, b.BookedDate))) AS RecID
FROM @TableA AS a
INNER JOIN @TableB AS b ON b.UserID = a.UserID
)

SELECT UserID,
BookedDateA,
BookedDateB
FROM Yak
WHERE RecID = 1[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 07:23:01

Also this:

SELECT t.UserID,tmp.BookedDate
FROM @TableA t
CROSS APPLY (SELECT TOP 1 BookedDate
             FROM @TableB
	         ORDER BY ABS(DATEDIFF(ss,t.BookedDate,BookedDate))) tmp

harleyquin
Starting Member

2 Posts

Posted - 2008-07-15 : 12:20:15

Worked a treat - thanks a million :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources