Update Query with Closest DateTime Matching with Inner Join

Question

I have the following two tables

CREATE TABLE Ep
    ([E] varchar(9), [M] varchar(9), [DTE] DATETIME)
;

INSERT INTO Ep
    ([E], [M], [DTE])
VALUES
    ('1595861-1', '1595861-1', CONVERT(datetime, '2002-11-26 14:18:00', 20)),
    ('1595904-1', '1595904-1', CONVERT(datetime, '2002-11-24 15:15:00', 20)),
    ('1596298-1', '1596298-1', CONVERT(datetime, '2002-12-17 11:12:00', 20)),
    ('1596357-1', '1596357-1', CONVERT(datetime, '2002-12-09 19:57:00', 20)),
    ('1596369-1', '1596369-1', CONVERT(datetime, '2002-12-11 06:00:00', 20)),
    ('1596370-1', '1596370-1', CONVERT(datetime, '2002-12-19 12:31:00', 20)),
    ('1596473-2', '1596473-1', CONVERT(datetime, '2002-12-15 08:39:00', 20)),
    ('1596473-3', '1596473-1', CONVERT(datetime, '2002-12-20 08:39:00', 20)),
    ('1596473-4', '1596473-1', CONVERT(datetime, '2002-12-13 08:39:00', 20)),
    ('1596473-5', '1596473-1', CONVERT(datetime, '2002-12-16 08:39:00', 20)),
    ('1596473-1', '1596473-1', CONVERT(datetime, '2002-12-14 08:39:00', 20))
;

CREATE TABLE Mp
    ([E] varchar(9), [M] varchar(9), [DTE] DATETIME)
;

INSERT INTO Mp
    ([E], [M], [DTE])
VALUES
    ('', '1595861-1', CONVERT(datetime, '2002-11-26 14:18:00', 20)),
    ('', '1595904-1', CONVERT(datetime, '2002-11-24 15:15:00', 20)),
    ('', '1596298-1', CONVERT(datetime, '2002-12-17 11:12:00', 20)),
    ('', '1596357-1', CONVERT(datetime, '2002-12-09 19:57:00', 20)),
    ('', '1596369-1', CONVERT(datetime, '2002-12-11 06:00:00', 20)),
    ('', '1596370-1', CONVERT(datetime, '2002-12-19 12:31:00', 20)),
    ('', '1596473-1', CONVERT(datetime, '2002-12-17 08:39:00', 20))
;

Currently I am updating the [E] field in the Mp table via a match on [M] where the DTE field (in Mp) is within a certian range (say +-3 days). The query to do this is currently

UPDATE [Mp] 
SET [E] = [Ep].[E] 
FROM [Mp] INNER JOIN [Ep] 
    ON [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN [Ep].[DTE] - 3 AND [Ep].[DTE] + 3;

This updates [Mp].[E] for [Mp].[M] = N'1596473-1' to 1596473-2. Essentailly the first entry SQL Server finds that is valid. However, I want to update this query so that SQL Server matches on the [M] field in the required date range (as it does now), but for the [Ep].[DTE] values that is closest to that in the [Mp].[DTE] value of 2002-12-17 08:39:00.

I have looked at adding a DATEDIFF clause, in the following way

UPDATE [Mp] 
SET [E] = [Ep].[E] 
FROM [Mp] INNER JOIN [Ep] 
    ON [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN [Ep].[DTE] - 3 AND [Ep].[DTE] + 3 
ORDER BY DATEDIFF(minutes, [Mp].[DTE], [Ep].[DTE]);

Clearly I can't do this, but I am unsure how to ammend this so that it works. The final data for [Mp] after the update should be

  1595861-1     1595861-1   2002-11-26 14:18:00.000 
  1595904-1     1595904-1   2002-11-24 15:15:00.000 
  1596298-1     1596298-1   2002-12-17 11:12:00.000 
  1596357-1     1596357-1   2002-12-09 19:57:00.000 
  1596369-1     1596369-1   2002-12-11 06:00:00.000 
  1596370-1     1596370-1   2002-12-19 12:31:00.000 
**1596473-5**   1596473-1   2002-12-17 08:39:00.000

Thanks for your time.

I can't actually bolt on the `ORDER BY` to an `UPDATE` query. The reason why I am asking is because I think that the underlying change might be very simple. I am fearful that doing what I want may involve `CTE`... — MoonKnight, Jan 31 '18 at 16:45
Ahh I see what you are saying. Give me a few for my actual job and I will see if I can help out. :) — Sean Lange, Jan 31 '18 at 16:48
@SeanLange thanks very much for your time, it is most appreciated. I think this might involve at least, some dynamic SQL to get the required date field into a tmp table that gets the closest match, then proceeding from there - I was just hoping that there was a quick fix... — MoonKnight, Jan 31 '18 at 16:53

score 5 · Accepted Answer · answered Jan 31 '18 at 17:00

Please first check which data following CTE produces as output

For nearest data, I used SQL ROW_NUMBER function with Partition By clause according to the time difference calculation fetched by DATEDIFF() function. To eliminate previous and following records, I used ABS() mathematical function.

select
*,
ROW_NUMBER() over (partition by [Mp].[M] order by abs(datediff(mi, [Mp].[DTE], [Ep].[DTE]))) as rn,
abs(datediff(mi, [Mp].[DTE], [Ep].[DTE])) diff
from Mp
left join Ep 
    on [Mp].[M] = [Ep].[M] 
WHERE [Mp].[DTE] BETWEEN dateadd(dd,-3,[Ep].[DTE]) AND dateadd(dd,3,[Ep].[DTE])

Then using the same CTE expression in an UPDATE command as following, you can populate the desired data into your target database table

;with cte as (
    select
        [Mp].[M] as M,
        [Ep].E as E,
    ROW_NUMBER() over (partition by [Mp].[M] order by abs(datediff(mi, [Mp].[DTE], [Ep].[DTE]))) as rn,
    abs(datediff(mi, [Mp].[DTE], [Ep].[DTE])) diff
    from Mp
    left join Ep 
        on [Mp].[M] = [Ep].[M] 
    WHERE [Mp].[DTE] BETWEEN dateadd(dd,-3,[Ep].[DTE]) AND dateadd(dd,3,[Ep].[DTE])
)
update [Mp]
set [E] = cte.E
from [Mp]
inner join cte on [Mp].M = cte.M and cte.rn = 1
where 
    cte.E is not null

After execution of the UPDATE statement, the target table data is as follows

I hope this is what you require

Damn, this is good. I just don't use SQL nearly enough to rattle this type of query off. Great stuff and thanks very much for your time, it is most appreciated. — MoonKnight, Jan 31 '18 at 22:21

Update Query with Closest DateTime Matching with Inner Join

1 Answers1