Return a specific value based on the min between 2 dates in SQL Server

Question

I have 2 tables :

CREATE TABLE #Table1(Name varchar(10), Date date, Type varchar(10));
INSERT INTO #TABLE1
VALUES
('aa', '2018-09-10', 'Client'),
('bb', '2018-10-12', 'Client'),
('cc', '2018-10-16', 'Client'),
('dd', '2018-11-12', 'Client'),
('ee', '2018-10-15', 'Client'), 
('ff', '2018-11-17', 'Client'),
('gg', '2018-12-12', 'Client'),
('tt', '2018-11-11', 'Server'), 
('kk', '2018-12-15', 'Server');

---

CREATE TABLE #Table2(ID INT, Type varchar(10), DateCreated date);
INSERT INTO #Table2
VALUES
(1,'Client', '2018-11-25'),
(2,'Client', '2018-10-25'),
(3,'Client', '2018-11-17'),
(4,'Client', '2018-09-25'),
(5,'Client', '2018-11-10'),
(6,'Client', '2018-12-05'),
(7,'Client', '2018-11-02'),
(8,'Server', '2018-11-19'),
(9,'Server', '2018-12-10');

---

Expected result :

ID Type Date Created Name
1 Client 2018年11月25日 ff
2 Client 2018年10月25日 cc
3 Client 2018年11月17日 ff
4 Client 2018年09月25日 aa
5 Client 2018年11月10日 cc
6 Client 2018年12月05日 ff
7 Client 2018年11月02日 cc
8 Server 2018年11月19日 tt
9 Server 2018年12月10日 tt

So basically I want for each ID from Table 2 the name from Table 1 based on the rule : If the type is the same , I want to return the name when the difference between Date Created and Date is min .

For example, for ID 6 , the query should return the name ff because the min difference between 05.12.2018 and any date from Table 1 is 18 . ( Date 17.11.2018 )

Date Created>= Date as a rule .

I've tried to create a SQL query but without results .

Can you please help me with this ?

• Laughing Vergil: Shouldn't the last result line be kk, not tt?
• carla: @LaughingVergil , no . Because Date Created should be > Date .
• Martin Smith: your expected result for item 5 contradicts that. It would have DateCreated = 2018年11月10日, Date = 2018年11月12日
• Aaron Bertrand: Row 3 also violates your rule, I get dd not ff for the expected row.
• carla: Yes , sorry . My mistake . Date Created should be >= Date . I modified now .

Answer 1

Given the sample data you provided (in a more regional-friendly format):

CREATE TABLE #Table1(name char(2),[date] date,type char(6));
INSERT #Table1 VALUES
('aa','20180910','Client'),('bb','20181012','Client'),('cc','20181016','Client'),
('dd','20181112','Client'),('ee','20181015','Client'),('ff','20181117','Client'),
('gg','20181212','Client'),('tt','20181111','Server'),('kk','20181215','Server');
CREATE TABLE #Table2(ID int,type char(6),[Date Created] date);
INSERT #Table2 VALUES
(1,'Client','20181125'),(2,'Client','20181025'),(3,'Client','20181117'),
(4,'Client','20180925'),(5,'Client','20181110'),(6,'Client','20181205'),
(7,'Client','20181102'),(8,'Server','20181119'),(9,'Server','20181210');
GO

The following query:

;WITH x AS 
(
 SELECT t2.ID, t2.type, t2.[Date Created], t1.name, 
 rn = ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY t1.date DESC) 
 FROM #Table1 AS t1 
 INNER JOIN #Table2 AS t2 
 ON t1.type = t2.type AND t2.[Date Created] >= t1.date
)
SELECT ID, type, [Date Created], name
 FROM x 
 WHERE rn = 1 
 ORDER BY ID;

Produces these results:

ID type Date Created name
-- ------ ------------ ----
1 Client 2018年11月25日 ff
2 Client 2018年10月25日 cc
3 Client 2018年11月17日 dd
4 Client 2018年09月25日 aa
5 Client 2018年11月10日 cc
6 Client 2018年12月05日 ff
7 Client 2018年11月02日 cc
8 Server 2018年11月19日 tt
9 Server 2018年12月10日 tt

Which differ from your expected results on row 3 and row 5, but I think that's a mistake in the expected results.

Answer 2

You could also use an APPLY - this would need an index on #Table1(Type, Date) INCLUDE (Name) to perform satisfactorily if the tables are at all large.

SELECT *
FROM #Table2 t2
 OUTER APPLY (SELECT TOP 1 Name
 FROM #Table1 t1
 WHERE t1.Type = t2.Type
 AND t1.Date <= T2.DateCreated
 ORDER BY t1.Date DESC) oa 

Another approach that could be considered is

WITH CTE1
 AS (SELECT ID,
 Type,
 DateCreated,
 NULL AS Name
 FROM #Table2
 UNION ALL
 SELECT NULL,
 Type,
 Date,
 NAME
 FROM #Table1),
 CTE2
 AS (SELECT ID,
 Type,
 DateCreated,
 SUBSTRING(MAX(FORMAT(DateCreated, 'yyyyMMdd') + Name)
 OVER (PARTITION BY Type ORDER BY DateCreated), 
 9, 50) AS Name
 FROM CTE1)
SELECT *
FROM CTE2
WHERE ID IS NOT NULL
ORDER BY ID 

If there is a unique covering index with key columns Type, Date on both tables the plan is quite efficient

enter image description here

• Aaron Bertrand: Your sort seems to be estimated to be more expensive than mine, but that is on a raw heap with no indexes, surely with suitable indexes that would change drastically. I have had a hard time adapting my habits to consider using APPLY.
• Martin Smith: yep - without any indexes it will need to sort for each row in the join so I wouldn't recommend this current one if they don't exist. If they do exist it is a nice seek on the inside of the join