asked on

SQL Script Help

I need to modify the below script so that is only shows the row with the newest invoice date.

Data:
Customer ID Invoice Date item id
1022 2011-05-01 ABC
4512 2009-08-12 ABC
1237 2014-01-20 ABC

Results needed: (I need the one with the newest invoice date)
1237 2014-01-20 ABC

My current script:
SELECT p21_view_invoice_hdr.customer_id, MAX(DISTINCT p21_view_invoice_hdr.invoice_date) AS invoice_date, p21_view_invoice_line.item_id
FROM p21_view_invoice_line INNER JOIN
p21_view_invoice_hdr ON p21_view_invoice_line.invoice_no = p21_view_invoice_hdr.invoice_no
GROUP BY p21_view_invoice_hdr.customer_id, p21_view_invoice_line.item_id

Problem with my script:
This script pulls all three since customer_id are all unique.

Lokesh B R

Hi,

try this

SELECT TOP 1 (p21_view_invoice_hdr.customer_id, MAX(DISTINCT p21_view_invoice_hdr.invoice_date) AS invoice_date, p21_view_invoice_line.item_id
FROM p21_view_invoice_line INNER JOIN
p21_view_invoice_hdr ON p21_view_invoice_line.invoice_no = p21_view_invoice_hdr.invoice_no
GROUP BY p21_view_invoice_hdr.customer_id, p21_view_invoice_line.item_id ORDER BY p21_view_invoice_hdr.invoice_date))

abarefoot

ASKER

Guess I need to change my request up a little.

Data:
Customer ID Invoice Date item id
1022 2011-05-01 ABC
4512 2009-08-12 ABC
1237 2014-01-20 ABC
5555 2003-04-15 123
8974 2011-05-12 123
5468 2006-04-19 123
1897 2013-08-09 6789

Results needed: (I need the one with the newest invoice date)
1237 2014-01-20 ABC
8974 2011-05-12 123
1897 2013-08-09 6789

Lokesh B R

Hi,

Please igone ) at the end of the query in my previous post.

abarefoot

ASKER

That didn't work. I had to do the below but that only pulls one row.

SELECT TOP (1)p21_view_invoice_hdr.customer_id, MAX(DISTINCT p21_view_invoice_hdr.invoice_date) AS invoice_date, p21_view_invoice_line.item_id
FROM p21_view_invoice_line INNER JOIN
p21_view_invoice_hdr ON p21_view_invoice_line.invoice_no = p21_view_invoice_hdr.invoice_no
GROUP BY p21_view_invoice_hdr.customer_id, p21_view_invoice_line.item_id

Scott Pletcher

SELECT customer_id, invoice_date, item_id
FROM (
SELECT ih.customer_id, ih.invoice_date, il.item_id,
--<other_columns_if_needed>,
ROW_NUMBER() OVER(PARTITION BY il.item_id ORDER BY ih.invoice_date DESC) AS row_num
FROM p21_view_invoice_line il INNER JOIN
p21_view_invoice_hdr ih ON il.invoice_no = ih.invoice_no
) AS derived_table
--ORDER BY ...

abarefoot

ASKER

Scott,

This is not working. It pulling everything and not showing only the most current invoice date.
Example of what it's pulling:

161216      2015-03-19 17:37:23.387      3M6900
161216      2015-03-30 13:44:35.517      3M6900
161838      2015-01-23 11:02:50.000      3M6900
161838      2015-04-03 14:36:49.000      3M6900
131498      2011-05-12 10:37:02.123      2245XXL
131498      2011-07-05 16:29:44.313      2245XXL
131498      2011-07-20 13:52:52.150      2245XXL
131498      2011-08-10 09:33:32.333      2245XXL
121550      2010-02-17 11:35:47.277      2245XXL
121550      2013-04-15 10:17:27.357      2245XXL
121550      2013-05-21 10:59:04.860      2245XXL

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enrmmoba

Hi

Use the max(date) parameter in the where condition rather than in your select parameter.
Example:

SELECT customer_id, invoice_date, item_id
FROM TABLE
where invoice_date = (select MAX(invoice_date) from table)

if items are from different tables use joins in select and where accordingly.

abarefoot

ASKER

Thanks for the help Scott. That last script is exactly what I needed.