UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Db2 SQL - Return Last Order Date Information    
 
   
edaroc
post Oct 25 2016, 09:53 AM
Post#1



Posts: 1,028
Joined: 27-July 04
From: Rochester NY USA


I need to come back to my Wizards at UtterAccess.
It's the old oldage, "Use it or lose it." I lost it from lack of practice.

Request is to list for each Item# each Customer, the Last Order Date, and the Quantity Shipped. Example at the end.

I have the "base" query. Now I need to take this to the final step of 1 Customer row per Item.

SELECT t0.ddaitx AS Item#,
t1.bwaqtx AS Customer,
t1.bwacdt AS OrderDate,
Sum(t0.ddarqt) AS QtyShp
FROM amflib.mbddrep t0
LEFT JOIN amflib.mbbwcpp t1
ON t1.bwaenb = t0.ddaenb
AND t1.bwdccd = t0.dddccd
AND t1.bwcvnb = t0.ddcvnb
WHERE t0.ddaitx <> ''
AND t0.dddccd = 1
AND t0.ddaenb = 1
GROUP BY t0.ddaitx,
t1.bwaqtx,
t1.bwacdt
ORDER BY t0.ddaitx,
t1.bwaqtx,
t1.bwacdt desc
;

In case you want an example.

If the results of the above query are:

ItemA, CustomerABC, 10/02/16, 150
ItemA, CustomerABC, 09/12/16, 175
ItemA, CustomerXYZ, 08/20,16, 400
ItemA, CustomerXYZ, 06/12/16, 200
ItemB, CustomerMMM, 09/09/16, 100
ItemB, CustomerXYZ, 09/01/16, 250
ItemB, CustomerXYZ, 08/08/16, 300

The results would be:

ItemA, CustomerABC, 10/10/16, 200
ItemA, CustomerXYZ, 08/20,16, 400
ItemB, CustomerMMM, 09/09/16, 100
ItemB, CustomerXYZ, 09/01/16, 250

Thank you! I miss you guys and gals.
Go to the top of the page
 
Doug Steele
post Oct 25 2016, 11:00 AM
Post#2


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Okay, I'm confused.

Based on the data you presented in the first table, I would expect the first row of the second table to be ItemA, CustomerABC, 09/12/16, 175, not ItemA, CustomerABC, 10/10/16, 200. I'd also expect the last row to be ItemB, CustomerXYZ, 08/08/16, 300, not ItemB, CustomerXYZ, 09/01/16, 250. Was this just incorrect copy and paste (or, in the case of the first one, cut and paste)?

Take a look at Query Pattern: Finding the maximum/minimum and getting the full row.

Post back if you can't figure out how to apply that approach to your problem! smile.gif
Go to the top of the page
 
edaroc
post Oct 25 2016, 11:20 AM
Post#3



Posts: 1,028
Joined: 27-July 04
From: Rochester NY USA


Hi Doug! Hope all is going well for you. Happy Fall!

My apologies. It looks like a did a Cut instead of a Copy.

Working on the link you gave me. Can't seem to get my head around this - that's my problem.
BUT! I appreciate your sending the technique and not just solving it for me.

Here's the results of the "core" query. I added a < to indicate those records that should be in the final results.
"For each Item list all the Customers and their last Order Dates and Qty Ordered"

ItemA, CustomerABC, 10/10/16, 200 <
ItemA, CustomerABC, 10/02/16, 150
ItemA, CustomerABC, 09/12/16, 175

ItemA, CustomerXYZ, 08/20,16, 400 <
ItemA, CustomerXYZ, 06/12/16, 200

ItemB, CustomerMMM, 09/09/16, 100 <

ItemB, CustomerXYZ, 09/01/16, 250 <
ItemB, CustomerXYZ, 08/08/16, 300
Go to the top of the page
 
edaroc
post Oct 25 2016, 12:46 PM
Post#4



Posts: 1,028
Joined: 27-July 04
From: Rochester NY USA


Doug - Got it!

thumbup.gif I still struggle constructing this type of request. I feel like there's some "understanding" to being able to create the query quicker. Is this a difficult one for everyone? Until you do them frequently enough that you become fluent?

Doug - Thanks for the guidance. I really appreciate it.

This is the query:

SELECT
q0.Item#,
q0.Customer,
q0.OrderDate,
q0.QtyShp
FROM
(
SELECT
t0.ddaitx As Item#,
t1.bwaqtx As Customer,
t1.bwacdt As OrderDate,
SUM(t0.ddarqt) As QtyShp
FROM amflib.mbddrep t0
LEFT JOIN amflib.mbbwcpp t1 ON t1.bwaenb = t0.ddaenb AND t1.bwdccd = t0.dddccd AND t1.bwcvnb = t0.ddcvnb
WHERE t0.ddaitx <> '' AND t0.dddccd = 1 AND t0.ddaenb = 1
GROUP BY t0.ddaitx, t1.bwaqtx, t1.bwacdt
ORDER BY t0.ddaitx, t1.bwaqtx, t1.bwacdt DESC
) q0
LEFT JOIN
(
SELECT
t0.ddaitx As Item#,
t1.bwaqtx As Customer,
MAX(t1.bwacdt) As OrderDate
FROM amflib.mbddrep t0
LEFT JOIN amflib.mbbwcpp t1 ON t1.bwaenb = t0.ddaenb AND t1.bwdccd = t0.dddccd AND t1.bwcvnb = t0.ddcvnb
WHERE t0.ddaitx <> '' AND t0.dddccd = 1 AND t0.ddaenb = 1
GROUP BY t0.ddaitx, t1.bwaqtx
) q1 ON q1.Item# = q0.Item# AND q1.Customer = q0.Customer
WHERE q0.OrderDate = q1.OrderDate
ORDER BY q0.Item#, q0.OrderDate DESC, q0.Customer
;
Go to the top of the page
 
Doug Steele
post Oct 25 2016, 12:48 PM
Post#5


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


So, you've got a query that returns the data in which you're interested:

CODE
SELECT t0.ddaitx AS Item#,
t1.bwaqtx AS Customer,
t1.bwacdt AS OrderDate,
Sum(t0.ddarqt) AS QtyShp
FROM amflib.mbddrep t0
LEFT JOIN amflib.mbbwcpp t1
ON t1.bwaenb = t0.ddaenb
AND t1.bwdccd = t0.dddccd
AND t1.bwcvnb = t0.ddcvnb
WHERE t0.ddaitx <> ''
AND t0.dddccd = 1
AND t0.ddaenb = 1
GROUP BY t0.ddaitx,
t1.bwaqtx,
t1.bwacdt
ORDER BY t0.ddaitx,
t1.bwaqtx,
t1.bwacdt desc


What you want to find, for each combination of Item# and Customer, the row that has the largest value for OrderDate. If you were to join the query to itself so that you could look at each row and compare the value of OrderDate for that row to the value of OrderDate for all other rows for that combination of Item# and Customer, you would be able to find the row of interest.

CODE
SELECT l.Item#, l.Customer, l.OrderDate AS LeftDate, r.OrderDate AS RightDate
FROM BaseQuery AS l LEFT JOIN BaseQuery AS r
ON l.Item# = r.Item#
AND l.Customer = r.Customer
AND l.OrderDate < r.OrderDate

When you run that query, you get

CODE
Item#  Customer     LeftDate  RightDate
ItemA  CustomerABC  10-10-16
ItemA  CustomerABC  10-02-16  10-10-16
ItemA  CustomerABC  09-12-16  10-02-16
ItemA  CustomerABC  09-12-16  10-10-16
ItemA  CustomerXYZ  08-20-16
ItemA  CustomerXYZ  06-12-16  08-20-16
ItemB  CustomerMMM  09-09-16
ItemB  CustomerXYZ  09-01-16
ItemB  CustomerXYZ  08-08-16  09-01-16

Note that the value of RightDate is Null for those rows where the LeftDate is the most recent date.

That means you can change the query above to

CODE
SELECT l.Item#, l.Customer, l.OrderDate AS LeftDate, l.QtyShip
FROM BaseQuery AS l LEFT JOIN BaseQuery AS r
ON l.Item# = r.Item#
AND l.Customer = r.Customer
AND l.OrderDate < r.OrderDate
WHERE r.OrderDate IS NULL
ORDER BY l.Item#, l.Customer

Running that gives you what you're looking for:

CODE
Item#  Customer     OrderDate  QtyShip
ItemA  CustomerABC  10-10-16       200
ItemA  CustomerXYZ  08-20-16       400
ItemB  CustomerMMM  09-09-16       100
ItemB  CustomerXYZ  09-01-16       250

So just plug the SQL for BaseQuery into that, and you should be good to go!

CODE
SELECT l.Item#, l.Customer, l.OrderDate AS LeftDate, l.QtyShip
FROM
(SELECT t0.ddaitx AS Item#,
t1.bwaqtx AS Customer,
t1.bwacdt AS OrderDate,
Sum(t0.ddarqt) AS QtyShp
FROM amflib.mbddrep t0
LEFT JOIN amflib.mbbwcpp t1
ON t1.bwaenb = t0.ddaenb
AND t1.bwdccd = t0.dddccd
AND t1.bwcvnb = t0.ddcvnb
WHERE t0.ddaitx <> ''
AND t0.dddccd = 1
AND t0.ddaenb = 1
GROUP BY t0.ddaitx,
t1.bwaqtx,
t1.bwacdt
) AS l
LEFT JOIN
(SELECT t0.ddaitx AS Item#,
t1.bwaqtx AS Customer,
t1.bwacdt AS OrderDate,
Sum(t0.ddarqt) AS QtyShp
FROM amflib.mbddrep t0
LEFT JOIN amflib.mbbwcpp t1
ON t1.bwaenb = t0.ddaenb
AND t1.bwdccd = t0.dddccd
AND t1.bwcvnb = t0.ddcvnb
WHERE t0.ddaitx <> ''
AND t0.dddccd = 1
AND t0.ddaenb = 1
GROUP BY t0.ddaitx,
t1.bwaqtx,
t1.bwacdt
) AS r
ON l.Item# = r.Item#
AND l.Customer = r.Customer
AND l.OrderDate < r.OrderDate
WHERE r.OrderDate IS NULL
ORDER BY l.Item#, l.Customer


(I hesitate to mention this, but this is Item 33 in our new book!)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th December 2017 - 10:46 AM