Conditional join on soonest date – SQL Server 2017

I am working with two tables:

tblOrders

| Part | Order |
|:----:|:-----:|
| AXXA | 10122 |
| AXXA | 10125 |
| AXXB | 10244 |
| AXXB | 10254 |
| AXXB | 10259 |

and tblPOs

| rOrder |  PONum  |   PODate   |
|:------:|:-------:|:----------:|
|  10122 | PO99283 |  2/6/2020  |
|  10125 | PO99283 |  2/6/2020  |
|  10244 | PO99455 |  3/22/2020 |
|  10244 | PO99456 |  3/11/2020 |
|  10254 | PO99019 |  1/22/2020 |
|  10259 | PO99122 | 12/22/2020 |
|  10259 | PO99122 |  1/2/2021  |

I am trying to join these two tables, and for each order, select the PO with the earliest arrival date (in the event there is more than one).

So for Order Number 10244, it has two POs, PO99455 and PO99456. I would want PO99456 to be associated, since it’s date of 03/11/2020 is sooner than PO99455‘s date of 03/22/2020.

An ideal resulting dataset would look like:

| PartNum | OrderNum |  PONum  |   PODate   |
|:-------:|:--------:|:-------:|:----------:|
|   AXXA  |   10122  | PO99283 |  2/6/2020  |
|   AXXA  |   10125  | PO99283 |  2/6/2020  |
|   AXXB  |   10244  | PO99456 |  3/11/2020 |
|   AXXB  |   10254  | PO99019 |  1/22/2020 |
|   AXXB  |   10259  | PO99122 | 12/22/2020 |

Currently, I have written:

SELECT
  a.PartNum,
  a.OrderNum,
  b.PONum,
  MAX(b.PODate) AS PODate

FROM
  tblOrders a
    LEFT JOIN tblPOs b ON a.OrderNum = b.rOrderNum

GROUP BY
  a.PartNum,
  a.OrderNum,
  b.PONum

Which yields:

PartNum     OrderNum    PONum   PODate
AXXA    10122   PO99283     2020-02-05
AXXA    10125   PO99283     2020-02-05
AXXB    10244   PO99455     2020-03-21
AXXB    10244   PO99456     2020-03-10
AXXB    10254   PO99019     2020-01-21
AXXB    10259   PO99122     2021-01-01

I’ve set up a SQL FIDDLE with this demo. How can I change my query to achieve the desired results?

Answer

Use OUTER APPLY:

SELECT o.*, po.*
FROM tblOrders o OUTER APPLY
     (SELECT TOP (1) po.*
      FROM tblPOs p
      WHERE p.OrderNum = o.rOrderNum
      ORDER BY PODate ASC
     ) po

Advertisement

Answer