Query closest date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query closest date

Author

Topic

luarit
Starting Member

6 Posts

Posted - 2012-03-13 : 10:49:59

Hello,

I have a problem with a query:

TABLE 1

Id Date Units Requested
1 1/01/2011 5
1 2/01/2011 3
1 3/01/2011 4
2 4/01/2011 7

TABLE 2

Id Date delivery
1 2/01/2011
1 3/01/2011
1 4/01/2011
2 5/01/2011

I want as output:

Id Date Date Delivery
1 1/01/2011 2/01/2011
1 2/01/2011 3/01/2011
1 3/01/2011 4/01/2011
2 4/01/2011 5/01/2011

That means I want to take all date delivery and know when was the request. The request is the closest date smaller with the same ID.

Any idea?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-13 : 12:13:06

[code]SELECT *
FROM Table1 AS T1
OUTER APPLY
(
SELECT MIN(T2.[Date delivery]) AS [Date delivery]
FROM Table2 AS T2
WHERE T1.[Date] < T2.[Date delivery]
) AS T

--EDIT here is some sample data in a consumable format incase anyone else wants to offer suggestions.

DECLARE @T1 TABLE (Id INT, [Date] DATE, [Units Requested] INT)
INSERT @T1 VALUES
(1, '1/01/2011', 5),
(1, '2/01/2011', 3),
(1, '3/01/2011', 4),
(2, '4/01/2011', 7)

DECLARE @T2 TABLE (Id INT, [Date delivery] DATE)
INSERT @T2 VALUES
(1, '2/01/2011'),
(1, '3/01/2011'),
(1, '4/01/2011'),
(2, '5/01/2011')
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources