QUERY - join two tables based on date and filter results

My apologies for the cryptic title. I guess my inability to condense my question into a title is indicative of my inability to come up with an answer to the following:

I have data in two tables (MS SQL Server 2012). A table produced and packaged.

The data in produced has an order number, product code and start date:

produced

pr_order |   code  |   startdate
---------------------------------
8000009  |   pr_12 |   2016-05-23
8000002  |   pr_12 |   2016-04-01
8000001  |   pr_12 |   2016-03-29

8000010  |   pr_10 |   2016-05-26
8000008  |   pr_10 |   2016-05-01
etc.

The data in packaged as an order number, produced product code, packaged product code and a start date:

packaged

pa_order |  pr_code |   pa_code  |  startdate
----------------------------------------------
7000100  |  pr_12   |   pa_999   |  2016-05-26
7000102  |  pr_12   |   pa_888   |  2016-05-24
7000098  |  pr_12   |   pa_777   |  2016-04-01
7000088  |  pr_12   |   pa_999   |  2016-03-31
7000104  |  pr_12   |   pa_808   |  2016-03-30

7000105  |  pr_10   |   pa_101   |  2016-05-26
7000109  |  pr_10   |   pa_202   |  2016-05-26
7000099  |  pr_10   |   pa_107   |  2016-05-26
7000095  |  pr_10   |   pa_202   |  2016-05-03
7000094  |  pr_10   |   pa_107   |  2016-05-02
7000093  |  pr_10   |   pa_666   |  2016-05-01
etc.

I'm trying to create a query that shows the produced order number for a packaged order number.


The following applies:

• packaged order pr_code == produced order code

• packaged order startdate >= produced order startdate

• but a packaged order is 'linked' to a produced order only when:

the packaged order startdate falls between the produced order startdates of two records with the same produced order code.

• produced order has one or more packaged order(s)

• packaged order has one produced order

The result should look something like the following:

```
pa_code | pr_code

Using the LEAD analytic function, you can produce the required results like this:

WITH
  prod AS
  (
    SELECT
      *,
      next_startdate = LEAD(startdate) OVER (PARTITION BY code
                                             ORDER BY startdate ASC)
    FROM
      dbo.produced
  )
SELECT
  pa.pa_code,
  pa.pr_code,
  pa.pa_order,
  pr.pr_order
FROM
  dbo.packaged AS pa
  INNER JOIN prod AS pr ON pa.pr_code = pr.code
    AND pa.startdate >= pr.startdate
    AND (pa.startdate < pr.next_startdate OR pr.next_startdate IS NULL)
;

Basically, the LEAD function is used to calculate the startdate of the next pr_order. The date is then used in the joining condition.

Note that the query uses an open-ended range: the next date, when it is not null, is compared using a strict inequality rather than a non-strict one, because otherwise some rows would be matched twice.

StackExchange Database Administrators Q#139698, answer score: 3