Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

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