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.
LVL 1
abarefootAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Lokesh B RDeveloperCommented:
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))
0
abarefootAuthor Commented:
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
0
Lokesh B RDeveloperCommented:
Hi,

Please igone ) at the end of the query in my previous post.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

abarefootAuthor Commented:
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
0
Scott PletcherSenior DBACommented:
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 ...
0
abarefootAuthor Commented:
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
0
Scott PletcherSenior DBACommented:
DOH!  Sorry, my bad, I left out the WHERE condition on row_num:

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
WHERE row_num = 1
 --ORDER BY ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enrmmobaCommented:
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.
0
abarefootAuthor Commented:
Thanks for the help Scott.  That last script is exactly what I needed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.