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 aproduced order
only when:the
packaged order startdate
falls between theproduced order startdates
of two records with the sameproduced order code
.produced order
has one or morepackaged order(s)
packaged order
has oneproduced 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!)