Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | The nearest date puzzle

In this puzzle you have two table – T2 & T1. Based on the dates from T1 table we need to pick the nearest date from table T2 which is greater then the t1 date.

Please check out the sample input values and sample expected output below.

Sample Input

T1

Id Dates
1 2017-01-01 14:11:22.990
2 2017-01-05 14:11:22.990
3 2017-01-10 14:11:22.990

T2

Id Dates
1 2017-01-01 14:11:22.990
2 2017-01-06 14:11:22.990
3 2017-01-07 14:11:22.990
4 2017-01-08 14:11:22.990
5 2017-01-09 14:11:22.990
6 2017-01-10 14:11:22.990
7 2017-01-11 14:11:22.990
8 2017-01-12 14:11:22.990
9 2017-01-13 14:11:22.990
10 2017-01-14 14:11:22.990

Expected Output

T1Id T1Date T2Id T2Date
1 2017-01-01 14:11:22.990 2 2017-01-06 14:11:22.990
2 2017-01-05 14:11:22.990 2 2017-01-06 14:11:22.990
3 2017-01-10 14:11:22.990 7 2017-01-11 14:11:22.990

Script

Use below script to create table and insert sample data into it.

--



CREATE TABLE T1
(
     Id INT
     ,Dates DATETIME
)
GO

INSERT INTO T1 VALUES 
(1,'2017-01-01 14:11:22.990'),
(2,'2017-01-05 14:11:22.990'),
(3,'2017-01-10 14:11:22.990')
GO

CREATE TABLE T2
(
     Id INT
     ,Dates DATETIME
)
GO

INSERT INTO T2 VALUES 
(1,'2017-01-01 14:11:22.990'),
(2,'2017-01-06 14:11:22.990'),
(3,'2017-01-07 14:11:22.990'),
(4,'2017-01-08 14:11:22.990'),
(5,'2017-01-09 14:11:22.990'),
(6,'2017-01-10 14:11:22.990'),
(7,'2017-01-11 14:11:22.990'),
(8,'2017-01-12 14:11:22.990'),
(9,'2017-01-13 14:11:22.990'),
(10,'2017-01-14 14:11:22.990')
GO


--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Solution 1


--

SELECT a.Id T1Id,a.Dates T1Date,k.Id T2Id ,k.Dates T2Date FROM T1 a
OUTER APPLY
(
	SELECT TOP 1 * FROM T2 b
	WHERE a.Dates < b.Dates
)k

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements