troubleshooting Question

SQL script question

Avatar of abarefoot
abarefoot asked on
Scripting LanguagesSQL
25 Comments1 Solution179 ViewsLast Modified:
Setup:
Below are the tables.  I need to find the last PO and Invoice for item 3m05620 which should be invoice 95.

p21_view_po_hdr    
po_no     supplier_id
1448099            123
1448099            123
1448099            123

p21_view_po_line
item_id                      po_no
3m05620            1448099
3m05620            1448099
3m05620            1448099
3m05620            1448099

p21_view_apinv_hdr
invoice_no      invoice_date      po_no
90            7/1/17            1448099
91            10/1/17            1448099
92            9/1/17            1448099
94            8/1/17            1448099
95            11/1/17            1448099
96            6/1/17            1448099

Results needed:
item               po_no            invoice_no       invoice date
3m05620       1448099            95                     11/1/17


Below is the script I have.  This script shows the last invoice_no is 96 and I want it to show the last invoice no based on invoice date so 95 since it has the most recent invoice date.

SELECT        p21_view_po_line.item_id, MAX(p21_view_po_hdr.po_no) AS po_no, p21_view_po_hdr.supplier_id, MAX(p21_view_apinv_hdr.invoice_no) AS invoice_no,
                         MAX(p21_view_apinv_hdr.invoice_date) AS invoice_date
FROM            p21_view_po_hdr INNER JOIN
                         p21_view_apinv_hdr ON p21_view_po_hdr.po_no = p21_view_apinv_hdr.po_no INNER JOIN
                         p21_view_po_line ON p21_view_po_hdr.po_no = p21_view_po_line.po_no
GROUP BY p21_view_po_line.item_id, p21_view_po_hdr.supplier_id
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 25 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 25 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros