SQL Correlated Subquery - Need help with MAX
I have two tables: "backorders" and "sku"
SKU
id | orderdate | sku | expectedship
unfulfilled
orderdate | ordernum | saleschannel | sku | expectedship
The sku table contains a list of items that have different expected shipping dates based on the order date.
eg.
1 | Apple | 01.08.2014 | 19.10.2014
2 | Apple | 02.08.2014 | 28.10.2014
3 | Pear | 02.08.2014 | 20.10.2014
4 | Grape | 02.08.2014 | 22.10.2014
The inverse records table lists the items that were ordered. Ordernum is a unique link where multiple items can be ordered.
eg.
02.08.2014 | order1 | a | apple
02.08.2014 | order1 | a | grape
02.08.2014 | order1 | a | pear
03.08.2014 | order2 | c | banana
What I am trying to achieve, for each backlog, is populating the maximum expected dispatch date for all items for this order.
For example.
02.08.2014 | order1 | a | apple | 28.10.2014
02.08.2014 | order1 | a | grape | 28.10.2014
02.08.2014 | order1 | a | pear | 28.10.2014
03.08.2014 | order2 | c | banana | NULL
As you can see above, order 1 shows the maximum / oldest date for all 3 sku sorted.
The following query shows me the expected dispatch date for each sku; however cannot decide how to get the maximum date for the order number.
SELECT
backorders.orderdate,
backorders.ordernum,
backorders.saleschannel,
backorders.sku,
setup.expectedship
FROM backorders
LEFT OUTER JOIN setup ON backorders.orderdate = setup.orderdate AND backorders.sku = setup.sku
WHERE (backorders.saleschannel = 'a')
OR (backorders.saleschannel ='b')
OR (backorders.saleschannel ='c')
ORDER BY backorders.ordernum DESC
I can also define the maximum date for each ordernum separately:
SELECT
backorders.ordernum,
MAX(setup.expectedship) AS `MAX(expectedship)`
FROM backorders
INNER JOIN setup ON backorders.sku = setup.sku AND backorders.orderdate = setup.orderdate
WHERE (setup.orderdate = backorders.orderdate)
AND (setup.sku = backorders.sku)
AND (backorders.saleschannel = 'a')
OR (backorders.saleschannel ='b')
OR (backorders.saleschannel ='c')
GROUP BY backorders.ordernum
Is there a way to run the first query to determine the expected ship per order line, and then a subquery to update the expected ship all-round to the maximum?
source to share
to what i meant in my comment, you could just join the two queries together ... i would try to replicate this to make sure it works .. but your date formats are off and i don't have time to formatting all of them.
SELECT
t.orderdate, t.ordernum,
t.saleschannel, t.sku,
t1.expectedship
FROM
( SELECT
b.orderdate, b.ordernum,
b.saleschannel, b.sku,
s.expectedship
FROM backorders b
LEFT OUTER JOIN setup s ON b.orderdate = s.orderdate AND b.sku = s.sku
WHERE b.saleschannel IN('a', 'b', 'c')
ORDER BY b.ordernum DESC
)t
LEFT JOIN
( SELECT
b.ordernum,
MAX(s.expectedship) AS expectedship
FROM backorders b
INNER JOIN setup s ON b.sku = s.sku AND b.orderdate = s.orderdate
WHERE (s.sku = b.sku)
AND b.saleschannel IN('a', 'b', 'c')
GROUP BY b.ordernum
)t1 on t1.ordernum = t.ordernum
order by ordernum;
source to share