Joining Based on the Max Date <= a Given Date

Question

I've got two tables that look something like this:

Table 1:

Unit      Date
A         9/30/17
B         9/30/17

Table 2:

Unit     Date      Metric
A        9/30/17   100
A        6/30/17   250
A        5/31/17   150
B        8/15/17   200
B        4/30/17   150
B        4/15/17   100

For each combination of Unit and Date from Table 1, I want to pull back the corresponding Metric value from Table 2. If there is not a match in Table 2, I want to pull back the Metric value for the next closest date that is < the date from Table 1. Using the examples above, I would expect to get 100 for Unit A and 200 for Unit B.

How can I get this into query form? I've been having a hard time wrapping my head around it. It would be easy if I was just dealing with dates, but the Unit component is what's throwing me off.

@a_horse_with_no_name We are on Netezza 7.2. – Eric J Dec 19 '17 at 21:29 — Eric J, Dec 19 '17 at 21:29

score 0 · Answer 1 · answered Dec 19 '17 at 21:40

To get the exact match is relatively easy by using a LEFT JOIN

SELECT t1.Unit, t2.Metric
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.Unit = t1.Unit AND t2.Date = t1.Date

With this query t2.Metric will be NULL if there is no exact match. To get the metric without an exact match is trickier. In MS SQL Server you could write a user defined function (UDF) to return the closest value.

create function dbo.udfGetMetric(@Unit char(1), @Date datetime) returns int
as 
BEGIN
declare @Metric smallint;
SELECT TOP(1) @Metric = Metric
FROM Table2 
WHERE Unit = @Unit
    and [Date] < @Date
ORDER BY [Date] DESC;

RETURN @Metric;
END
GO

You can then call this function in your query when the value is NULL

SELECT t1.Unit, ISNULL(t2.Metric, dbo.udfGetMetric(t1.Unit, t1.[Date])) as Metric
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.Unit = t1.Unit AND t2.[Date] = t1.[Date];

You can do the same by using a subquery instead of a function:

SELECT t1.Unit
, ISNULL(t2.Metric, (SELECT TOP(1) Metric
                    FROM Table2 
                    WHERE Unit = t1.Unit
                        and [Date] < t1.[Date]
                    ORDER BY [Date] DESC)) as Metric
FROM Table1 t1 
LEFT JOIN Table2 t2 ON t2.Unit = t1.Unit AND t2.[Date] = t1.[Date];

Joining Based on the Max Date <= a Given Date

1 Answers1