0

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 ?

  • Shouldn't the last result line be kk, not tt? – Laughing Vergil Apr 24 '19 at 16:13
  • @LaughingVergil , no . Because Date Created should be > Date . – carla Apr 24 '19 at 16:23
  • 1
    your expected result for item 5 contradicts that. It would have DateCreated = 2018-11-10, Date = 2018-11-12 – Martin Smith Apr 24 '19 at 16:47
  • Row 3 also violates your rule, I get dd not ff for the expected row. – Aaron Bertrand Apr 24 '19 at 16:49
  • Yes , sorry . My mistake . Date Created should be >= Date . I modified now . – carla Apr 24 '19 at 16:51
2

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.

  • Thanks Aaron :) – carla Apr 24 '19 at 16:57
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

  • 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. – Aaron Bertrand Apr 24 '19 at 17:00
  • 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 – Martin Smith Apr 24 '19 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.