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 ?

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