QUERY - join two tables based on date and filter results

Question

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 |   pa_order |   pr_order
--------------------------------------------------
pa_999  |   pr_12   |   7000100  |   8000009
pa_999  |   pr_12   |   7000088  |   8000001
pa_888  |   pr_12   |   7000102  |   8000009
pa_808  |   pr_12   |   7000104  |   8000001
pa_777  |   pr_12   |   7000098  |   8000002
pa_202  |   pr_10   |   7000109  |   8000010
pa_202  |   pr_10   |   7000095  |   8000008
etc.

Basically, I'm trying to create an overview of which production order a packaging order belongs to, given the startdates of both production and packaging orders.

I've tried (lots of variations of) the following (fiddle here):

SELECT      p.pa_code
           ,p.pr_code
           ,p.pa_order
           ,r.pr_order
FROM        packaged p 
JOIN        produced r 
    ON      p.pr_code = r.code
    AND     p.startdate <= r.startdate
    AND     p.startdate >   (
                                SELECT  ISNULL(MAX(o.startdate),r.startdate)
                                FROM    produced o 
                                WHERE   o.code = p.pr_code
                                    AND o.startdate < r.startdate
                            )
ORDER BY    p.pa_order DESC;

Which results in (lots of variations of) this:

pa_code | pr_code | pa_order |  pr_order
--------|---------|----------|----------
pa_202  | pr_10   | 7000109  |  8000010    correct
pa_101  | pr_10   | 7000105  |  8000010    correct
pa_808  | pr_12   | 7000104  |  8000002    wrong
pa_999  | pr_12   | 7000100  |  8000009    correct
pa_107  | pr_10   | 7000099  |  8000010    correct
pa_777  | pr_12   | 7000098  |  8000002    correct
pa_202  | pr_10   | 7000095  |  8000010    wrong
pa_107  | pr_10   | 7000094  |  8000010    wrong
pa_999  | pr_12   | 7000088  |  8000002    wrong

--and order 7000093 is missing...

I just don't understand how I can link a packaged order to a production order based on the startdates of the orders. (A packaged order is 'linked' to a produced order only when the packaged startdate is equal to or greater than a corresponding produced order and the startdate of that packaged order is lower than the startdate of the 'next' (if any) produced order. (I hope that makes sense...)

The correct result based on the example data should be:

pa_code | pr_code | pa_order |  pr_order
--------|---------|----------|----------
pa_202  | pr_10   | 7000109  |  8000010    
pa_101  | pr_10   | 7000105  |  8000010    
pa_808  | pr_12   | 7000104  |  8000001    
pa_999  | pr_12   | 7000100  |  8000009    
pa_107  | pr_10   | 7000099  |  8000010    
pa_777  | pr_12   | 7000098  |  8000002    
pa_202  | pr_10   | 7000095  |  8000008    
pa_107  | pr_10   | 7000094  |  8000008  
pa_666  | pr_10   | 7000093  |  8000008      
pa_999  | pr_12   | 7000088  |  8000001    

Can anyone help me understand how I can get a correct result set? (Sorry for the long question!)

Answer 1

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.

Answer 2

So, after lots of hard thinking and trial and error (mostly error I must admit), it seems I stumbled upon an answer to my question.

I did the following:

1. create a temp table will hold the result, let's call it result.
2. fill result with the packaged data.
3. update the temp table with the produced order.

The temp table:

CREATE TABLE #result
(  
     pa_order INT NOT NULL  
    ,pr_code NVARCHAR(5) NOT NULL  
    ,pa_code NVARCHAR(6) NOT NULL  
    ,startdate DATE NOT NULL  
    ,pr_order INT NULL  
);

I use this query (no fiddle (yet) as I keep getting gateway timeouts) (fiddle here):

UPDATE  p
    SET p.pr_order =    
    (
        SELECT  MIN(r.pr_order) [pr_order]
        FROM    produced r
        WHERE   r.code = p.pr_code
            AND 
            ( 
                r.startdate >=  
                (
                    SELECT  ISNULL(MAX(r.startdate), p.startdate)
                    FROM    produced r
                    WHERE   r.code = p.pr_code
                    AND r.startdate <= p.startdate
                )
                AND
                r.startdate <=  
                (
                    SELECT  ISNULL(MAX(pr.startdate), r.startdate)
                    FROM    produced pr
                    WHERE   pr.code = p.pr_code
                    AND pr.startdate > r.startdate
                )
            )
    )
FROM    #result p;

This produces the following result:

pa_code  | pr_code  | pa_order  |   pr_order
---------|----------|-----------|----------
pa_202   | pr_10    | 7000109   |   8000010
pa_101   | pr_10    | 7000105   |   8000010
pa_808   | pr_12    | 7000104   |   8000001
pa_888   | pr_12    | 7000102   |   8000009
pa_999   | pr_12    | 7000100   |   8000009
pa_107   | pr_10    | 7000099   |   8000010
pa_777   | pr_12    | 7000098   |   8000002
pa_202   | pr_10    | 7000095   |   8000008
pa_107   | pr_10    | 7000094   |   8000008
pa_666   | pr_10    | 7000093   |   8000008
pa_999   | pr_12    | 7000088   |   8000001

Which is what I expected. I'm not sure if this is the most elegant or 'best' solution but for me, it works.