SQL script question

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
LVL 1
abarefootAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

aikimarkCommented:
You need a where clause that should look something like this:
Where p21_view_apinv_hdr.invoice_date = (Select Max(invoice_date) 
From p21_view_apinv_hdr 
Where p21_view_apinv_hdr.[po_no] = 1448099
)

Open in new window

0
aikimarkCommented:
If you are doing this for all purchase orders in the table, you would probably want to include a group by query in your outer-most From clause to include the max(invoice_date) value in each row
0
abarefootAuthor Commented:
Aikimark,

I think that will work.  I plan on testing it on Monday but so far it looks like that's exactly what I'm looking for.
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Pawan KumarDatabase ExpertCommented:
Harcoded where clause is not good.  Also you must be having multiple po_no's and item_id's then the above solution will not work. Are you using SQL Server or any other DB ?

Multiple tested solutions for you - First one should work in all the DBs. Also 1st, 3rd and 4th should work in SQL Server even if you have multiple po_no's. Please try below solutions and let us know in case of any issues.

SOLUTION 1

--

SELECT k.item_id,k.po_no,k.supplier_id,t.invoice_date,t.invoice_no FROM 
(
	SELECT pv.po_no,pv.supplier_id,pl.item_id  
	FROM            
		p21_view_po_hdr pv INNER JOIN
		p21_view_po_line pl ON pv.po_no = pl.po_no
	GROUP BY pv.po_no,pv.supplier_id,pl.item_id  
)k 
INNER JOIN
(
	SELECT u.invoice_date,u.po_no,hdr.invoice_no FROM 
	(
		SELECT po_no, MAX(invoice_date) invoice_date FROM p21_view_apinv_hdr ph
		GROUP BY po_no
	)u 
	INNER JOIN p21_view_apinv_hdr hdr ON u.po_no = hdr.po_no AND u.invoice_date = hdr.invoice_date
)t ON k.po_no = t.po_no

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
item_id         po_no                supplier_id invoice_date            invoice_no
--------------- -------------------- ----------- ----------------------- -----------
3m05620         1448099              123         2017-11-01 00:00:00.000 95

(1 row(s) affected)

Open in new window



SOLUTION 2

SELECT TOP 1 p21_view_po_line.item_id, (p21_view_po_hdr.po_no) AS po_no, p21_view_po_hdr.supplier_id
		, (p21_view_apinv_hdr.invoice_no) AS invoice_no, 
          (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
ORDER BY invoice_date DESC

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
item_id         po_no                supplier_id invoice_no  invoice_date
--------------- -------------------- ----------- ----------- -----------------------
3m05620         1448099              123         95          2017-11-01 00:00:00.000

(1 row(s) affected)

Open in new window


SOLUTION 3

SELECT item_id, po_no,supplier_id,invoice_no,invoice_date
FROM 
(
	SELECT p21_view_po_line.item_id, (p21_view_po_hdr.po_no) AS po_no, p21_view_po_hdr.supplier_id
		, (p21_view_apinv_hdr.invoice_no) AS invoice_no, 
          (p21_view_apinv_hdr.invoice_date) AS invoice_date 
		  , ROW_NUMBER() OVER (PARTITION BY p21_view_po_line.item_id, (p21_view_po_hdr.po_no) 
			ORDER BY p21_view_apinv_hdr.invoice_date DESC)rnk
	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 
)u WHERE rnk = 1

Open in new window



OUTPUT

/*------------------------
OUTPUT
------------------------*/
item_id         po_no                supplier_id invoice_no  invoice_date
--------------- -------------------- ----------- ----------- -----------------------
3m05620         1448099              123         95          2017-11-01 00:00:00.000

(1 row(s) affected)

Open in new window


SOLUTION 4

--

SELECT * FROM 
(
	SELECT pv.po_no,pv.supplier_id,pl.item_id  
	FROM            
		p21_view_po_hdr pv INNER JOIN
		p21_view_po_line pl ON pv.po_no = pl.po_no
	GROUP BY pv.po_no,pv.supplier_id,pl.item_id  
)k 
CROSS APPLY
(
	SELECT TOP 1 invoice_no , invoice_date FROM p21_view_apinv_hdr ph
	WHERE ph.po_no = k.po_no
	ORDER BY invoice_date DESC
)t

Open in new window


OUTPUT

/*------------------------
OUTPUT
------------------------*/
po_no                supplier_id item_id         invoice_no  invoice_date
-------------------- ----------- --------------- ----------- -----------------------
1448099              123         3m05620         95          2017-11-01 00:00:00.000

(1 row(s) affected)

Open in new window

0
abarefootAuthor Commented:
update info:

I need the parameter on supplier ID.  I'm using SQL 2012

aikimark,

Your solution is putting the same invocie_date for every item so that's not going to work.

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
Where p21_view_apinv_hdr.invoice_date = (SELECT        MAX(p21_view_apinv_hdr.invoice_date) AS Expr1
FROM            p21_view_apinv_hdr INNER JOIN
                         p21_view_po_hdr ON p21_view_apinv_hdr.po_no = p21_view_po_hdr.po_no
WHERE        (supplier_id = 101479)
)
GROUP BY p21_view_po_line.item_id, p21_view_po_hdr.supplier_id




Kumar,

Your solutions have dups on the item_id.  I need one line per item id.  I tried using a distinct but that didn't help.
0
Pawan KumarDatabase ExpertCommented:
Can u please show few rows of output u r getting from my query and what u need?
0
abarefootAuthor Commented:
If there is a better way to get this to you let me know.  I'm needing there to be no dups for item_id.  the report has a parameter based on supplier id.  I need to find the most current invoice and the associated PO.    If that doesn't make since let me know.  

solution 1:
item_id	po_no	supplier_id	invoice_date	invoice_no
KLE600-8	1175011	100866	7/27/2009	422075
AJA876-BC	1278241	100197	2/15/2013	650346
IRW11SP	1130119	100143	1/11/2008	59146001
CEVO100PD	1128977	100346	12/27/2007	207536
ASIBBG-20	1356154	100177	2/13/2015	187193
IES9012-2XL	1369703	100432	6/26/2015	732417
MSASCH3187511	1179255	101002	9/24/2009	94419360
ALG2041-12K	1151433	100122	9/4/2008	320217
BTPDARWB-ATI	1104671	100709	4/4/2007	230377
MAKBJR181Z	1339922	100937	9/19/2014	MA- 176491- 14
RADRWG531-M	1350188	101202	12/16/2014	55241683
RADRWG531-M	1350188	101202	12/16/2014	55241760
KLED213-9NE-CR	1211754	100866	11/19/2010	938562
MIL2697-22CT	1262149	100977	8/2/2012	13952455
MAXSV22-2ZGM-L-AGL	1332485	101202	8/11/2014	55166098
MAXSV22-2ZGM-L-AGL	1332485	101202	8/11/2014	55166117
CRWSS110	1188027	100960	2/8/2010	90914959
DBI1246012	1133009	100506	2/12/2008	714472
IES2229-2XL	1275899	100774	1/10/2013	6936990
IES2229-2XL	1275899	100774	1/10/2013	6936740
LAGCLO41773	1173646	100880	7/9/2009	1102927800

Open in new window

0
abarefootAuthor Commented:
solution 2:
item_id	po_no	supplier_id	invoice_no	invoice_date
RAGCLFW25	 1232510	120535	                4218	          2039-12-31 00:00:00.000

Open in new window

0
abarefootAuthor Commented:
solution 3:

3M2060-48MM	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M2091	1471293	101479	AW67731	2017-11-15 00:00:00.000
3MBP-17IS	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M6800	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M6900	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M6900PF	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M77	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M8550	1471293	101479	AW67731	2017-11-15 00:00:00.000
3MCP040NA	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M2090-48A-CP	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M2090-36A	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M9211+	1471293	101479	AW67731	2017-11-15 00:00:00.000
3M6800	1472045	101479	AG41035	2017-11-15 00:00:00.000
3M6894	1472045	101479	AG41035	2017-11-15 00:00:00.000
3M6897	1472045	101479	AG41035	2017-11-15 00:00:00.000
3M6898	1472045	101479	AG41035	2017-11-15 00:00:00.000
3M6900	1472045	101479	AG41035	2017-11-15 00:00:00.000
3M024-00-02R01	1472045	101479	AG41035	2017-11-15 00:00:00.000
EAR318-1001	1472045	101479	AG41035	2017-11-15 00:00:00.000
3MP2000	1472045	101479	AG41035	2017-11-15 00:00:00.000
3M6800PF	1470207	101479	CUPO1470207	2017-11-15 00:00:00.000
3M6900PF	1470207	101479	CUPO1470207	2017-11-15 00:00:00.000
3M6800PF-PROMO	1470207	101479	CUPO1470207	2017-11-15 00:00:00.000
3M5N11	1470558	101479	AW61852	2017-11-14 00:00:00.000
3M6800	1470558	101479	AW61852	2017-11-14 00:00:00.000
3M6885	1470558	101479	AW61852	2017-11-14 00:00:00.000
EAR318-1002	1470558	101479	AW61852	2017-11-14 00:00:00.000
PELH7B	1470558	101479	AW61852	2017-11-14 00:00:00.000
3M80513	1470558	101479	AW61852	2017-11-14 00:00:00.000
3M06-0000-30	1470558	101479	AW61852	2017-11-14 00:00:00.000
3MM-926/37322	1470558	101479	AW61852	2017-11-14 00:00:00.000
3M26-0099-35SW	1470558	101479	AW61852	2017-11-14 00:00:00.000
3MM-976	1470558	101479	AW61852	2017-11-14 00:00:00.000
3MM-206	1470558	101479	AW61852	2017-11-14 00:00:00.000
3M453-03-01R06	1471937	101479	AG40497	2017-11-14 00:00:00.000

Open in new window

0
abarefootAuthor Commented:
solution 4:  Found a better way to display the info.

po_no                                   supplier_id                             item_id                                  invoice_no                       invoice_date
--------------------------------------- --------------------------------------- ---------------------------------------- -------------------------------- -----------------------
1021164                                 100122                                   ALG8413-05                              00215898                         2004-07-28 00:00:00.000
1306021                                 100322                                   BAT1RCH5                                RV1306021                        2013-10-31 00:00:00.000
1006674                                 100551                                   CMC321000                               147706                           2004-03-09 00:00:00.000
1402475                                 101283                                   CMC321000                               51930927                         2016-04-05 00:00:00.000
1045991                                 100506                                   DBI5900551                              497924                           2005-05-18 00:00:00.000
1009981                                 101520                                   DRLSC-3                                 303408                           2004-03-24 00:00:00.000
1011458                                 101520                                   DRLSC-3                                 323664                           2004-04-19 00:00:00.000
1020635                                 100952                                   DRLSC-3                                 MC210180                         2004-07-21 00:00:00.000
1026137                                 101520                                   DRLSC-3                                 0449079                          2004-09-28 00:00:00.000
1028593                                 101520                                   DRLSC-3                                 0477307                          2004-11-08 00:00:00.000
1097747                                 115655                                   FLRBX320-PP                             712241                           2006-12-27 00:00:00.000
1015872                                 100723                                   HAW9082                                 240785                           2004-05-27 00:00:00.000
1040197                                 100819                                   JET02552                                301560                           2005-03-08 00:00:00.000
1301492                                 100322                                   JMT-EGLOVETEST                          KM1301492                        2013-10-31 00:00:00.000
1308856                                 100322                                   JMT-EGLOVETEST                          KM1308856                        2013-12-31 00:00:00.000
1321665                                 100322                                   JMT-EGLOVETEST                          KM1321665                        2014-05-31 00:00:00.000
1333529                                 100322                                   JMT-EGLOVETEST                          KM1333529                        2014-08-31 00:00:00.000
1343537                                 100322                                   JMT-EGLOVETEST                          KM1343537/1346867                2014-11-30 00:00:00.000
1071836                                 100322                                   PARTSCLEANER                            MF1071836                        2006-03-30 00:00:00.000
1074247                                 101097                                   PEAHB200L5                              1373463001                       2006-03-27 00:00:00.000
1043360                                 101002                                   SET UP CHARGE                           92487743                         2005-04-18 00:00:00.000
1289286                                 101002                                   SET UP CHARGE                           97026460                         2013-06-10 00:00:00.000
1021493                                 100075                                   SLIN2EE3208                             44787                            2004-08-03 00:00:00.000
1045374                                 101414                                  #17                                      0043798                          2005-05-04 00:00:00.000
1118382                                 101414                                  #17                                      0063018                          2007-08-20 00:00:00.000
1099252                                 100322                                  #2 BLASTING SAND                         MH1099252                        2007-01-29 00:00:00.000
1183333                                 101411                                  #2PENCIL                                 1183333CU                        2009-11-21 00:00:00.000
1268329                                 101646                                  #2PENCIL                                 9946318434                       2012-10-09 00:00:00.000
1157936                                 100322                                  #2PENCILS                                PO#1157936LStrege                2008-11-12 00:00:00.000
1116575                                 100322                                  #3 BLASTING SAND                         BC1116575                        2007-07-28 00:00:00.000
1070533                                 100322                                  #4 BLASTING SAND                         BC1070533                        2006-02-28 00:00:00.000
1100866                                 100322                                  #4 BLASTING SAND                         BC1100866                        2007-02-28 00:00:00.000
1110543                                 100322                                  #4 BLASTING SAND                         MS1110543                        2007-05-29 00:00:00.000
1115786                                 100322                                  #4 BLASTING SAND                         BC1115786                        2007-07-28 00:00:00.000
1100866                                 100322                                  #6 BLASTING SAND                         BC1100866                        2007-02-28 00:00:00.000
1110543                                 100322                                  #6 BLASTING SAND                         MS1110543                        2007-05-29 00:00:00.000
1267401                                 131804                                  %DELATD5704                              986560                           2012-09-28 00:00:00.000
1299433                                 144692                                  %DIXSFM025                               CLUPO1299433                     2014-06-26 00:00:00.000
1281180                                 101446                                  %HT-11250-12-T-I                         1099961                          2013-03-04 00:00:00.000
1301103                                 101446                                  %HT-11250-12-T-I                         1105133                          2013-09-12 00:00:00.000
1093265                                 100170                                  %PLOY%HANGER                             1082419001                       2006-10-30 00:00:00.000
1301947                                 101390                                  %SMALLORDERCHARGE                        0174188-IN                       2013-09-24 00:00:00.000
1274451                                 100322                                  %THINNER                                 GC1274451                        2012-12-31 00:00:00.000
1275068                                 100322                                  %THINNER                                 MH1275068                        2012-12-31 00:00:00.000
1368420                                 100690                                  %THINNER                                 1174577-01                       2015-06-04 00:00:00.000
1217927                                 101279                                  %WYATTSAFETY%                            0074391-IN                       2011-02-17 00:00:00.000
1219153                                 101279                                  %WYATTSAFETY%                            0078580-in                       2011-10-07 00:00:00.000
1224790                                 129991                                  %WYATTSAFETY%                            77220                            2011-05-13 00:00:00.000
1226630                                 129991                                  %WYATTSAFETY%                            77619                            2011-06-24 00:00:00.000
1002636                                 100880                                  0103602                                  4549729-00                       2003-12-29 00:00:00.000

Open in new window

0
abarefootAuthor Commented:
Here's solution 1:

item_id                                  po_no                                   supplier_id                             invoice_date            invoice_no
---------------------------------------- --------------------------------------- --------------------------------------- ----------------------- --------------------------------
2WW4020-XL                               1029662                                 101527                                  2004-11-05 00:00:00.000 78514
2WW7040-L                                1007092                                 101527                                  2004-02-18 00:00:00.000 73020
2WW7040-L                                1010336                                 101527                                  2004-03-25 00:00:00.000 73838
2WW7040-L                                1014439                                 101527                                  2004-05-24 00:00:00.000 75012
2WW7040-L                                1019854                                 101527                                  2004-07-14 00:00:00.000 75992
2WW7040-L                                1034438                                 101527                                  2005-01-07 00:00:00.000 79910
2WW7040-L                                1034995                                 101527                                  2005-01-11 00:00:00.000 79967
2WW7040-L                                1044772                                 101527                                  2005-04-29 00:00:00.000 82549
2WW7040-L                                1049109                                 101527                                  2005-06-15 00:00:00.000 83584
2WW7040-L                                1058867                                 101527                                  2005-10-04 00:00:00.000 85844
2WW7040-L                                1090051                                 101527                                  2006-09-26 00:00:00.000 94508
2WW7040-L                                1091170                                 100478                                  2006-10-06 00:00:00.000 171394
2WW7040-L                                1091170                                 100478                                  2006-10-06 00:00:00.000 171408
2WW7040-L                                1092137                                 101527                                  2006-10-18 00:00:00.000 95145
2WW7040-L                                1106313                                 101527                                  2007-04-04 00:00:00.000 99684
2WW7040-L                                1108680                                 101527                                  2007-05-02 00:00:00.000 100642
2WW7040-L                                1138194                                 101527                                  2008-04-14 00:00:00.000 109989
2WW7040-L                                1139745                                 101527                                  2008-05-02 00:00:00.000 110567
2WW7040-L                                1143748                                 101527                                  2008-06-16 00:00:00.000 111696
2WW7040-M                                1090051                                 101527                                  2006-09-26 00:00:00.000 94508
2WW7040-M                                1131486                                 101527                                  2008-01-28 00:00:00.000 107832
2WW7040PXXXL                             1290049                                 100322                                  2013-06-30 00:00:00.000 MW1290049
2WW7040-XL                               1007092                                 101527                                  2004-02-18 00:00:00.000 73020
2WW7040-XL                               1010336                                 101527                                  2004-03-25 00:00:00.000 73838
2WW7040-XL                               1014439                                 101527                                  2004-05-24 00:00:00.000 75012
2WW7040-XL                               1034438                                 101527                                  2005-01-07 00:00:00.000 79910
2WW7040-XL                               1034995                                 101527                                  2005-01-11 00:00:00.000 79967
2WW7040-XL                               1038219                                 101527                                  2005-02-16 00:00:00.000 80880
2WW7040-XL                               1044772                                 101527                                  2005-04-29 00:00:00.000 82549
2WW7040-XL                               1057412                                 101527                                  2005-09-15 00:00:00.000 85464
2WW7040-XL                               1058867                                 101527                                  2005-10-04 00:00:00.000 85844
2WW7040-XL                               1066652                                 101527                                  2006-01-09 00:00:00.000 88163
2WW7040-XL                               1074887                                 101527                                  2006-04-04 00:00:00.000 90420
2WW7040-XL                               1078100                                 101527                                  2006-05-10 00:00:00.000 91356
2WW7040-XL                               1090051                                 101527                                  2006-09-26 00:00:00.000 94508
2WW7040-XL                               1090340                                 101527                                  2006-09-28 00:00:00.000 94601
2WW7040-XL                               1090933                                 101527                                  2006-10-04 00:00:00.000 94738
2WW7040-XL                               1091170                                 100478                                  2006-10-06 00:00:00.000 171394
2WW7040-XL                               1091170                                 100478                                  2006-10-06 00:00:00.000 171408
2WW7040-XL                               1091179                                 100049                                  2006-10-06 00:00:00.000 167296
2WW7040-XL                               1092631                                 101527                                  2006-10-24 00:00:00.000 95294
2WW7040-XL                               1094658                                 101527                                  2006-11-17 00:00:00.000 96100
2WW7040-XL                               1105283                                 101527                                  2007-03-28 00:00:00.000 99542
2WW7040-XL                               1115737                                 101527                                  2007-07-23 00:00:00.000 102891
2WW7040-XL                               1125448                                 101527                                  2007-11-06 00:00:00.000 105711
2WW7040-XL                               1128670                                 101527                                  2007-12-18 00:00:00.000 106907
2WW7040-XL                               1130073                                 101527                                  2008-01-30 00:00:00.000 107950
2WW7040-XL                               1132782                                 101527                                  2008-02-13 00:00:00.000 108299
2WW7040-XL                               1136650                                 101527                                  2008-03-27 00:00:00.000 109521
2WW7040-XL                               1138194                                 101527                                  2008-04-14 00:00:00.000 109989

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Which column(s) are duplicate ? Which solution number needs to be modified.
0
abarefootAuthor Commented:
solution 3:

item_id                                  po_no                                   supplier_id                             invoice_no                       invoice_date
---------------------------------------- --------------------------------------- --------------------------------------- -------------------------------- -----------------------
 ALG8413-05                              1021164                                 100122                                  00215898                         2004-07-28 00:00:00.000
 BAT1RCH5                                1306021                                 100322                                  RV1306021                        2013-10-31 00:00:00.000
 CMC321000                               1006674                                 100551                                  147706                           2004-03-09 00:00:00.000
 CMC321000                               1402475                                 101283                                  51930927                         2016-04-05 00:00:00.000
 DBI5900551                              1045991                                 100506                                  497924                           2005-05-18 00:00:00.000
 DRLSC-3                                 1009981                                 101520                                  303408                           2004-03-24 00:00:00.000
 DRLSC-3                                 1011458                                 101520                                  323664                           2004-04-19 00:00:00.000
 DRLSC-3                                 1020635                                 100952                                  MC210180                         2004-07-21 00:00:00.000
 DRLSC-3                                 1026137                                 101520                                  0449079                          2004-09-28 00:00:00.000
 DRLSC-3                                 1028593                                 101520                                  0477307                          2004-11-08 00:00:00.000
 FLRBX320-PP                             1097747                                 115655                                  712241                           2006-12-27 00:00:00.000
 HAW9082                                 1015872                                 100723                                  240785                           2004-05-27 00:00:00.000
 JET02552                                1040197                                 100819                                  301560                           2005-03-08 00:00:00.000
 JMT-EGLOVETEST                          1301492                                 100322                                  KM1301492                        2013-10-31 00:00:00.000
 JMT-EGLOVETEST                          1308856                                 100322                                  KM1308856                        2013-12-31 00:00:00.000
 JMT-EGLOVETEST                          1321665                                 100322                                  KM1321665                        2014-05-31 00:00:00.000
 JMT-EGLOVETEST                          1333529                                 100322                                  KM1333529                        2014-08-31 00:00:00.000
 JMT-EGLOVETEST                          1343537                                 100322                                  KM1343537/1346867                2014-11-30 00:00:00.000
 PARTSCLEANER                            1071836                                 100322                                  MF1071836                        2006-03-30 00:00:00.000
 PEAHB200L5                              1074247                                 101097                                  1373463001                       2006-03-27 00:00:00.000
 SET UP CHARGE                           1043360                                 101002                                  92487743                         2005-04-18 00:00:00.000
 SET UP CHARGE                           1289286                                 101002                                  97026460                         2013-06-10 00:00:00.000
 SLIN2EE3208                             1021493                                 100075                                  44787                            2004-08-03 00:00:00.000
#17                                      1045374                                 101414                                  0043798                          2005-05-04 00:00:00.000
#17                                      1118382                                 101414                                  0063018                          2007-08-20 00:00:00.000
#2 BLASTING SAND                         1099252                                 100322                                  MH1099252                        2007-01-29 00:00:00.000
#2PENCIL                                 1183333                                 101411                                  1183333CU                        2009-11-21 00:00:00.000
#2PENCIL                                 1268329                                 101646                                  9946318426                       2012-10-09 00:00:00.000
#2PENCILS                                1157936                                 100322                                  PO#1157936LStrege                2008-11-12 00:00:00.000
#3 BLASTING SAND                         1116575                                 100322                                  BC1116575                        2007-07-28 00:00:00.000
#4 BLASTING SAND                         1070533                                 100322                                  BC1070533                        2006-02-28 00:00:00.000
#4 BLASTING SAND                         1100866                                 100322                                  BC1100866                        2007-02-28 00:00:00.000
#4 BLASTING SAND                         1110543                                 100322                                  MS1110543                        2007-05-29 00:00:00.000
#4 BLASTING SAND                         1115786                                 100322                                  BC1115786                        2007-07-28 00:00:00.000
#6 BLASTING SAND                         1100866                                 100322                                  BC1100866                        2007-02-28 00:00:00.000
#6 BLASTING SAND                         1110543                                 100322                                  MS1110543                        2007-05-29 00:00:00.000
%DELATD5704                              1267401                                 131804                                  986560                           2012-09-28 00:00:00.000
%DIXSFM025                               1299433                                 144692                                  CLUPO1299433                     2014-06-26 00:00:00.000
%HT-11250-12-T-I                         1281180                                 101446                                  1099961                          2013-03-04 00:00:00.000
%HT-11250-12-T-I                         1301103                                 101446                                  1105133                          2013-09-12 00:00:00.000
%PLOY%HANGER                             1093265                                 100170                                  1082419001                       2006-10-30 00:00:00.000
%SMALLORDERCHARGE                        1301947                                 101390                                  0174188-IN                       2013-09-24 00:00:00.000
%THINNER                                 1274451                                 100322                                  GC1274451                        2012-12-31 00:00:00.000
%THINNER                                 1275068                                 100322                                  MH1275068                        2012-12-31 00:00:00.000
%THINNER                                 1368420                                 100690                                  1174577-01                       2015-06-04 00:00:00.000
%WYATTSAFETY%                            1217927                                 101279                                  0074391-IN                       2011-02-17 00:00:00.000
%WYATTSAFETY%                            1219153                                 101279                                  0078580-in                       2011-10-07 00:00:00.000
%WYATTSAFETY%                            1224790                                 129991                                  77220                            2011-05-13 00:00:00.000
%WYATTSAFETY%                            1226630                                 129991                                  77619                            2011-06-24 00:00:00.000
0103602                                  1002636                                 100880                                  4549729-00                       2003-12-29 00:00:00.000

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Please try this updated..

We just need to Group By and aggregate functions to handle things like this -

SELECT 
	 MAX(po_no) po_no                     
	,supplier_id              
	,item_id                   
	,MAX(invoice_no) invoice_no 
	,MAX(invoice_date) invoice_date 
FROM 
(
	SELECT pv.po_no,pv.supplier_id,pl.item_id  
	FROM            
		p21_view_po_hdr pv INNER JOIN
		p21_view_po_line pl ON pv.po_no = pl.po_no
	GROUP BY pv.po_no,pv.supplier_id,pl.item_id  
)k 
CROSS APPLY
(
	SELECT TOP 1 invoice_no , invoice_date FROM p21_view_apinv_hdr ph
	WHERE ph.po_no = k.po_no
	ORDER BY invoice_date DESC
)t
GROUP BY item_id,supplier_id

Open in new window

0
abarefootAuthor Commented:
Kumar,

I like the looks of solution 3 just because i understand it better but anything to get the job done is fine at this point.   I've modified the report to only look at supplier_id = 101479 as supplier_id will be the parameter the user inputs into the report.   I want to only see a distinct item_id and no duplicates in Item_ID.  You can see that the below has multiple with the same item_id.  I need the most recent invoice_date and the invoice_no and PO_NO that goes with that most recent invoice_date.

SELECT top 200 item_id, po_no,supplier_id,invoice_no,invoice_date
FROM 
(
	SELECT p21_view_po_line.item_id, (p21_view_po_hdr.po_no) AS po_no, p21_view_po_hdr.supplier_id
		, (p21_view_apinv_hdr.invoice_no) AS invoice_no, 
          (p21_view_apinv_hdr.invoice_date) AS invoice_date 
		  , ROW_NUMBER() OVER (PARTITION BY p21_view_po_line.item_id, (p21_view_po_hdr.po_no) 
			ORDER BY p21_view_apinv_hdr.invoice_date DESC)rnk
	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 
)u WHERE rnk = 1 and supplier_id = 101479

Open in new window



item_id                                  po_no                                   supplier_id                             invoice_no                       invoice_date
---------------------------------------- --------------------------------------- --------------------------------------- -------------------------------- -----------------------
143130REPAIR-01                          1215867                                 101479                                  AW68794                          2011-03-10 00:00:00.000
3M REPAIR MOTOR                          1378681                                 101479                                  CLUP1378681                      2015-10-29 00:00:00.000
3M REPAIRS                               1167233                                 101479                                  OG42504                          2009-04-17 00:00:00.000
3M REPAIRS                               1422647                                 101479                                  AW81463                          2016-09-21 00:00:00.000
3M REPAIRS                               1441181                                 101479                                  OG53945                          2017-03-23 00:00:00.000
3M-453-03-02R06                          1419004                                 101479                                  AW70927                          2016-08-29 00:00:00.000
3M-972                                   1361291                                 101479                                  AW79925                          2015-04-29 00:00:00.000
3M-972                                   1398814                                 101479                                  AW10054                          2016-03-18 00:00:00.000
3M-H-111-100                             1062226                                 101479                                  OG52359                          2005-11-09 00:00:00.000
3M00-048011-05975-4                      1182286                                 101479                                  AW50562                          2009-11-06 00:00:00.000
3M00-051131-97329-9                      1344422                                 101479                                  AF71176                          2014-10-23 00:00:00.000
3M00-051138-72194-7                      1254283                                 101479                                  AW68574                          2012-05-21 00:00:00.000
3M00-051138-72194-7                      1292041                                 101479                                  AW29251                          2013-06-18 00:00:00.000
3M00048011-05680-7                       1205537                                 101479                                  OG87522                          2010-09-08 00:00:00.000
3M00048011-05680-7                       1271541                                 101479                                  OG43084                          2012-11-14 00:00:00.000
3M00048011-05680-7                       1319041                                 101479                                  AB10533                          2014-03-25 00:00:00.000
3M00048011-05680-7                       1340459                                 101479                                  AW05441                          2014-09-25 00:00:00.000
3M00048011-05680-7                       1447780                                 101479                                  AF02662                          2017-04-28 00:00:00.000
3M00051141-274072                        1298506                                 101479                                  AF54285                          2013-08-27 00:00:00.000
3M00051141-274072                        1303509                                 101479                                  AW68616                          2013-10-08 00:00:00.000
3M00051141-274072                        1311041                                 101479                                  AG17434                          2014-01-14 00:00:00.000
3M00051141-274072                        1316900                                 101479                                  AG44085                          2014-02-20 00:00:00.000
3M00051141-274072                        1323259                                 101479                                  AG74129                          2014-04-18 00:00:00.000
3M00051141-274072                        1328418                                 101479                                  OG57581                          2014-06-10 00:00:00.000
3M00051141-274072                        1331095                                 101479                                  OG65636                          2014-06-30 00:00:00.000
3M00051141-274072                        1334954                                 101479                                  AF31119                          2014-08-06 00:00:00.000
3M00051141-274072                        1346244                                 101479                                  AW27573                          2014-11-14 00:00:00.000
3M00051141-274072                        1349748                                 101479                                  AW41872                          2014-12-22 00:00:00.000
3M00051141-274072                        1352814                                 101479                                  AW53522                          2015-01-19 00:00:00.000
3M00051141-274072                        1355076                                 101479                                  AW60812                          2015-02-06 00:00:00.000
3M00051141-274072                        1359349                                 101479                                  AW74394                          2015-03-27 00:00:00.000
3M00051141-274072                        1362066                                 101479                                  AG62520                          2015-04-13 00:00:00.000
3M00051141-274072                        1368005                                 101479                                  AG83736                          2015-06-02 00:00:00.000
3M00051141-274072                        1374889                                 101479                                  OG31589                          2015-08-04 00:00:00.000
3M00051141-274072                        1380314                                 101479                                  OG47079                          2015-09-16 00:00:00.000
3M00051141-274072                        1385103                                 101479                                  OG59780                          2015-10-27 00:00:00.000
3M00051141-274072                        1389688                                 101479                                  OG73027                          2016-01-28 00:00:00.000
3M00051141-274072                        1391701                                 101479                                  OG79608                          2016-01-07 00:00:00.000
3M00051141-274072                        1396109                                 101479                                  AW01390                          2016-02-12 00:00:00.000
3M00051141-274072                        1399882                                 101479                                  AG25117                          2016-03-15 00:00:00.000
3M00051141-274072                        1404976                                 101479                                  AW27608                          2016-04-27 00:00:00.000
3M00051141-274072                        1407850                                 101479                                  AW36767                          2016-05-20 00:00:00.000
3M00051141-274072                        1415459                                 101479                                  AW60244                          2016-07-28 00:00:00.000
3M00051141-274072                        1418308                                 101479                                  AW68691                          2016-08-18 00:00:00.000
3M00051141-274072                        1420650                                 101479                                  AF30036                          2016-09-08 00:00:00.000
3M00051141-274072                        1425962                                 101479                                  AF55357                          2016-10-17 00:00:00.000
3M00051141-274072                        1426451                                 101479                                  AF57913                          2016-10-21 00:00:00.000
3M00051141-274072                        1430683                                 101479                                  OG14678                          2016-11-30 00:00:00.000
3M00051141-274072                        1433662                                 101479                                  OG23751                          2016-12-28 00:00:00.000
3M00051141-274072                        1437296                                 101479                                  OG34588                          2017-02-03 00:00:00.000
3M00051141-274072                        1445560                                 101479                                  OG61303                          2017-04-12 00:00:00.000
3M00051141-274072                        1448246                                 101479                                  OG71585                          2017-05-04 00:00:00.000
3M00051141-274072                        1451281                                 101479                                  OG84518                          2017-06-06 00:00:00.000
3M00051141-274072                        1453318                                 101479                                  AW03605                          2017-06-19 00:00:00.000
3M00051141-274072                        1455937                                 101479                                  AW13186                          2017-07-13 00:00:00.000
3M00051141-274072                        1467497                                 101479                                  AW52483                          2017-10-12 00:00:00.000
3M00051141556000                         1437946                                 101479                                  AG38719                          2017-03-09 00:00:00.000
3M00051141556000                         1461641                                 101479                                  AW34275                          2017-09-07 00:00:00.000
3M00059                                  1254273                                 101479                                  AW62193                          2012-05-03 00:00:00.000
3M005111531452                           1453941                                 101479                                  AF51669                          2017-06-21 00:00:00.000
3M005111531452                           1454867                                 101479                                  AF58408                          2017-06-28 00:00:00.000
3M005111531452                           1464073                                 101479                                  AW41841                          2017-09-27 00:00:00.000
3M00524                                  1223765                                 101479                                  AB04553                          2011-06-14 00:00:00.000
3M00524                                  1239882                                 101479                                  OG83991                          2011-11-08 00:00:00.000
3M00524                                  1287046                                 101479                                  AF14599                          2013-05-14 00:00:00.000
3M00524                                  1383974                                 101479                                  AF58419                          2015-10-16 00:00:00.000
3M00524                                  1461982                                 101479                                  AG00492                          2017-09-11 00:00:00.000
3M007-00-15R01                           1116801                                 101479                                  SP15794                          2007-08-29 00:00:00.000
3M007-00-15R01                           1118877                                 101479                                  SP50019                          2007-09-24 00:00:00.000
3M007-00-15R01                           1391695                                 101479                                  OG79598                          2016-01-07 00:00:00.000
3M007-00-15R01                           1470224                                 101479                                  AW60517                          2017-11-08 00:00:00.000
3M0070015R01                             1004843                                 101479                                  78690                            2004-03-02 00:00:00.000
3M00941                                  1082397                                 101479                                  AF79680                          2006-09-27 00:00:00.000
3M00979                                  1211622                                 101479                                  AF27918                          2010-11-19 00:00:00.000
3M00979                                  1308377                                 101479                                  AG04026                          2014-03-06 00:00:00.000
3M01117                                  1196567                                 101479                                  OG40851                          2010-05-24 00:00:00.000
3M01117                                  1200730                                 101479                                  OG62948                          2010-07-12 00:00:00.000
3M01117                                  1210440                                 101479                                  AF22419                          2010-11-05 00:00:00.000
3M01117                                  1216383                                 101479                                  AB04007                          2011-02-17 00:00:00.000
3M01117                                  1221220                                 101479                                  AW80039                          2011-05-03 00:00:00.000
3M01117                                  1223765                                 101479                                  AB04553                          2011-06-14 00:00:00.000
3M01117                                  1230086                                 101479                                  AG40383                          2011-07-15 00:00:00.000
3M01117                                  1230676                                 101479                                  AB05244                          2011-09-02 00:00:00.000
3M01117                                  1236819                                 101479                                  OG70835                          2011-10-04 00:00:00.000
3M01117                                  1245944                                 101479                                  AW22863                          2012-02-02 00:00:00.000
3M01117                                  1250282                                 101479                                  AF44718                          2012-03-21 00:00:00.000
3M01117                                  1250900                                 101479                                  AF48749                          2012-04-11 00:00:00.000
3M01117                                  1254935                                 101479                                  AF64214                          2012-05-14 00:00:00.000
3M01117                                  1259620                                 101479                                  AF85140                          2012-07-13 00:00:00.000
3M01117                                  1260171                                 101479                                  AW87624                          2012-07-13 00:00:00.000
3M01117                                  1275960                                 101479                                  AG61195                          2013-01-11 00:00:00.000
3M01117                                  1306447                                 101479                                  AB09792                          2014-01-22 00:00:00.000
3M01117                                  1306768                                 101479                                  AW80161                          2013-11-15 00:00:00.000
3M01117                                  1373814                                 101479                                  AF18784                          2015-07-23 00:00:00.000
3M01117                                  1386258                                 101479                                  OG63575                          2015-11-05 00:00:00.000
3M01117                                  1395106                                 101479                                  AB15216                          2016-02-25 00:00:00.000
3M01117                                  1396719                                 101479                                  AW02838                          2016-02-17 00:00:00.000
3M01117                                  1442827                                 101479                                  OG52668                          2017-03-27 00:00:00.000
3M01318                                  1096000                                 101479                                  ClnupPO#1096000                  2007-04-04 00:00:00.000
3M01319                                  1096000                                 101479                                  ClnupPO#1096000                  2007-04-04 00:00:00.000
3M01397                                  1117358                                 101479                                  KL84794                          2007-08-08 00:00:00.000
3M01397                                  1360936                                 101479                                  AW78671                          2015-04-02 00:00:00.000
3M01397                                  1376574                                 101479                                  AF29667                          2015-08-14 00:00:00.000
3M01397                                  1395967                                 101479                                  AG10723                          2016-02-11 00:00:00.000
3M01397                                  1406843                                 101479                                  AW33597                          2016-05-12 00:00:00.000
3M01397                                  1426204                                 101479                                  AF56406                          2016-10-19 00:00:00.000
3M01397                                  1443223                                 101479                                  OG53587                          2017-03-23 00:00:00.000
3M01397                                  1443455                                 101479                                  OG54116                          2017-03-28 00:00:00.000
3M01397                                  1456091                                 101479                                  AW13654                          2017-07-13 00:00:00.000
3M01397                                  1468411                                 101479                                  AG25722                          2017-10-17 00:00:00.000
3M01398                                  1085475                                 101479                                  DU22997                          2006-08-07 00:00:00.000
3M01406                                  1117615                                 101479                                  SP04011                          2007-08-14 00:00:00.000
3M01406                                  1121916                                 101479                                  EK03265                          2007-10-03 00:00:00.000
3M01406                                  1129821                                 101479                                  GE50433                          2008-01-09 00:00:00.000
3M01407                                  1082044                                 101479                                  PO#1082044CU                     2006-12-16 00:00:00.000
3M01407                                  1107168                                 101479                                  RB74354                          2007-04-16 00:00:00.000
3M01407                                  1109648                                 101479                                  XE74339                          2007-05-14 00:00:00.000
3M01407                                  1121916                                 101479                                  EK03265                          2007-10-03 00:00:00.000
3M01407                                  1127068                                 101479                                  BM22810                          2007-11-28 00:00:00.000
3M01407                                  1129821                                 101479                                  GE50433                          2008-01-09 00:00:00.000
3M01434                                  1006563                                 101479                                  OG44483                          2003-09-19 00:00:00.000
3M01434                                  1033180                                 101479                                  OG55309                          2004-12-23 00:00:00.000
3M01435                                  1005597                                 101479                                  72093                            2004-02-04 00:00:00.000
3M01435                                  1007134                                 101479                                  76985                            2004-02-23 00:00:00.000
3M01438                                  1005597                                 101479                                  72093                            2004-02-04 00:00:00.000
3M01440                                  1006563                                 101479                                  OG44483                          2003-09-19 00:00:00.000
3M01440                                  1033180                                 101479                                  OG55309                          2004-12-23 00:00:00.000
3M01442                                  1006563                                 101479                                  OG44483                          2003-09-19 00:00:00.000
3M01442                                  1033180                                 101479                                  OG55309                          2004-12-23 00:00:00.000
3M01443                                  1006563                                 101479                                  OG44483                          2003-09-19 00:00:00.000
3M01443                                  1033180                                 101479                                  OG55309                          2004-12-23 00:00:00.000
3M01549                                  1123401                                 101479                                  EK59352                          2007-11-02 00:00:00.000
3M01550                                  1107168                                 101479                                  RB74354                          2007-04-16 00:00:00.000
3M01550                                  1123401                                 101479                                  EK59352                          2007-11-02 00:00:00.000
3M01550                                  1129821                                 101479                                  GE50433                          2008-01-09 00:00:00.000
3M01550                                  1133628                                 101479                                  AF43082                          2008-03-07 00:00:00.000
3M01552                                  1123401                                 101479                                  EK59352                          2007-11-02 00:00:00.000
3M01552                                  1133628                                 101479                                  AF43082                          2008-03-07 00:00:00.000
3M01668                                  1362979                                 101479                                  TG80242                          2015-04-21 00:00:00.000
3M01668                                  1463265                                 101479                                  AW39423                          2017-09-16 00:00:00.000
3M01987                                  1109648                                 101479                                  XE74339                          2007-05-14 00:00:00.000
3M01987                                  1122673                                 101479                                  EK30647                          2007-11-06 00:00:00.000
3M01987                                  1127068                                 101479                                  BM22810                          2007-11-28 00:00:00.000
3M01988                                  1109648                                 101479                                  XE74339                          2007-05-14 00:00:00.000
3M01988                                  1110520                                 101479                                  HP17855                          2007-05-24 00:00:00.000
3M01988                                  1127068                                 101479                                  BM22810                          2007-11-28 00:00:00.000
3M01988                                  1137401                                 101479                                  MK80914                          2008-04-08 00:00:00.000
3M01989                                  1359989                                 101479                                  AW75826                          2015-03-27 00:00:00.000
3M01989                                  1364350                                 101479                                  AW89363                          2015-05-04 00:00:00.000
3M01989                                  1375834                                 101479                                  OG34434A                         2015-08-11 00:00:00.000
3M01989                                  1379019                                 101479                                  AF39035                          2015-09-03 00:00:00.000
3M01989                                  1381378                                 101479                                  OG50284                          2015-09-25 00:00:00.000
3M01991                                  1110081                                 101479                                  HP00096                          2007-05-23 00:00:00.000
3M01991                                  1119092                                 101479                                  SP59657                          2007-08-30 00:00:00.000
3M01991                                  1127068                                 101479                                  BM22810                          2007-11-28 00:00:00.000
3M01991                                  1165463                                 101479                                  AF47397                          2009-04-02 00:00:00.000
3M01991                                  1352196                                 101479                                  AW52117                          2015-03-06 00:00:00.000
3M01991                                  1366343                                 101479                                  OG06455                          2015-05-20 00:00:00.000
3M01991                                  1368198                                 101479                                  OG11123                          2015-06-12 00:00:00.000
3M01991                                  1370711                                 101479                                  OG19064                          2015-06-24 00:00:00.000
3M01991                                  1376181                                 101479                                  OG40827A                         2015-09-02 00:00:00.000
3M01994                                  1226046                                 101479                                  OG18447                          2011-05-27 00:00:00.000
3M01994                                  1233042                                 101479                                  OG52747                          2011-08-18 00:00:00.000
3M01994                                  1257952                                 101479                                  AW77994                          2012-06-15 00:00:00.000
3M01994                                  1264053                                 101479                                  AG11726                          2012-08-24 00:00:00.000
3M01994                                  1272803                                 101479                                  AG47799                          2012-11-29 00:00:00.000
3M01994                                  1276667                                 101479                                  AG63616                          2013-01-18 00:00:00.000
3M01994                                  1290615                                 101479                                  AF25834                          2013-06-10 00:00:00.000
3M01994                                  1292533                                 101479                                  AF32953                          2013-06-26 00:00:00.000
3M01994                                  1305699                                 101479                                  AF83681                          2013-10-29 00:00:00.000
3M01994                                  1317130                                 101479                                  OG23134                          2014-03-04 00:00:00.000
3M01994                                  1320268                                 101479                                  OG32204                          2014-03-27 00:00:00.000
3M01994                                  1346874                                 101479                                  AW30168                          2014-11-14 00:00:00.000
3M01994                                  1352181                                 101479                                  AW51489                          2015-03-10 00:00:00.000
3M01994                                  1357810                                 101479                                  AG43699                          2015-03-04 00:00:00.000
3M01994                                  1374659                                 101479                                  AF22292                          2015-07-30 00:00:00.000
3M01994                                  1378015                                 101479                                  AF34725                          2015-08-27 00:00:00.000
3M01994                                  1386587                                 101479                                  OG64290                          2015-11-09 00:00:00.000
3M01994                                  1393678                                 101479                                  OG84633                          2016-01-27 00:00:00.000
3M01994                                  1396589                                 101479                                  AG13178                          2016-02-18 00:00:00.000
3M01994                                  1410662                                 101479                                  AW45985                          2016-06-16 00:00:00.000
3M01994                                  1425601                                 101479                                  AW89019                          2016-10-21 00:00:00.000
3M01994                                  1439397                                 101479                                  OG40913                          2017-02-18 00:00:00.000
3M01994                                  1454575                                 101479                                  AW08068                          2017-06-29 00:00:00.000
3M01994                                  1463057                                 101479                                  AG04471                          2017-09-21 00:00:00.000
3M02004                                  1096000                                 101479                                  ClnupPO#1096000                  2007-04-04 00:00:00.000
3M02007                                  1133628                                 101479                                  AF43082                          2008-03-07 00:00:00.000
3M02014                                  1104205                                 101479                                  VC57148                          2007-03-14 00:00:00.000
3M02016                                  1133628                                 101479                                  AF43082                          2008-03-07 00:00:00.000
3M02018                                  1104205                                 101479                                  VC57148                          2007-03-14 00:00:00.000
3M02018                                  1133628                                 101479                                  AF43082                          2008-03-07 00:00:00.000
3M02063                                  1111429                                 101479                                  HP54576                          2007-06-06 00:00:00.000
3M02066                                  1111429                                 101479                                  HP54576                          2007-06-06 00:00:00.000
3M021-14-00R01                           1116801                                 101479                                  SP15794                          2007-08-29 00:00:00.000
3M021-41-02-R01                          1136612                                 101479                                  MK50462                          2008-04-04 00:00:00.000
3M021-41-02R01                           1167818                                 101479                                  OG40608                          2009-04-21 00:00:00.000
3M021-41-02R01                           1196462                                 101479                                  OG39964                          2010-05-25 00:00:00.000
3M021-41-02R01                           1305701                                 101479                                  AW75774                          2013-10-30 00:00:00.000
3M021-41-02R01                           1421216                                 101479                                  AW76933                          2016-09-21 00:00:00.000
3M021200-02876-2                         1214604                                 101479                                  AW43895                          2011-01-10 00:00:00.000

(200 row(s) affected)

Open in new window

0
abarefootAuthor Commented:
Kumar,

Can I schedule you so we can get this fixed today?
0
abarefootAuthor Commented:
I modified the last script to only show supplier_id = 101479 and it ran for over 2 min before I canceled it.  Maybe I have my where statement in the wrong spot

SELECT 
	 MAX(po_no) po_no                     
	,supplier_id              
	,item_id                   
	,MAX(invoice_no) invoice_no 
	,MAX(invoice_date) invoice_date 
FROM 
(
	SELECT pv.po_no,pv.supplier_id,pl.item_id  
	FROM            
		p21_view_po_hdr pv INNER JOIN
		p21_view_po_line pl ON pv.po_no = pl.po_no
	GROUP BY pv.po_no,pv.supplier_id,pl.item_id  
)k 
CROSS APPLY
(
	SELECT TOP 1 invoice_no , invoice_date FROM p21_view_apinv_hdr ph
	WHERE ph.po_no = k.po_no
	ORDER BY invoice_date DESC
)t
where supplier_id = 101479
GROUP BY item_id,supplier_id

Open in new window

0
abarefootAuthor Commented:
I'm going to lunch but will be back in an hour if you want to schedule something.
0
Pawan KumarDatabase ExpertCommented:
Please try this updated-

SELECT * FROM 
(
	SELECT item_id, MAX(invoice_date) invoice_date , MAX(po_no) po_no
	FROM 
	(
		SELECT p21_view_po_line.item_id, (p21_view_po_hdr.po_no) AS po_no, p21_view_po_hdr.supplier_id
			, (p21_view_apinv_hdr.invoice_no) AS invoice_no, 
			  (p21_view_apinv_hdr.invoice_date) AS invoice_date 
			  , ROW_NUMBER() OVER (PARTITION BY p21_view_po_line.item_id, (p21_view_po_hdr.po_no) 
				ORDER BY p21_view_apinv_hdr.invoice_date DESC)rnk
		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 
	)u WHERE rnk = 1 and supplier_id = 101479
	GROUP BY item_id
)u
CROSS APPLY
(
	SELECT MAX(pv.invoice_no) invoice_no 
	FROM            
		p21_view_po_hdr pv 
	WHERE pv.invoice_date = u.invoice_date AND pv.po_no = u.po_no
)t

Open in new window

0
abarefootAuthor Commented:
I get the below error.  I'm looking at it also.

Msg 207, Level 16, State 1, Line 23
Invalid column name 'invoice_date'.
Msg 207, Level 16, State 1, Line 20
Invalid column name 'invoice_no'.
0
abarefootAuthor Commented:
I think this is what I needed to do.


SELECT * FROM 
(
	SELECT supplier_id, item_id, MAX(invoice_date) invoice_date , MAX(po_no) po_no
	FROM 
	(
		SELECT p21_view_po_line.item_id, (p21_view_po_hdr.po_no) AS po_no, p21_view_po_hdr.supplier_id
			, (p21_view_apinv_hdr.invoice_no) AS invoice_no, 
			  (p21_view_apinv_hdr.invoice_date) AS invoice_date 
			  , ROW_NUMBER() OVER (PARTITION BY p21_view_po_line.item_id, (p21_view_po_hdr.po_no) 
				ORDER BY p21_view_apinv_hdr.invoice_date DESC)rnk
		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 
	)u WHERE rnk = 1 and supplier_id = 101479
	GROUP BY supplier_id, item_id
)u
CROSS APPLY
(
SELECT        MAX(p21_view_apinv_hdr.invoice_no) AS invoice_no
FROM            p21_view_po_hdr AS pv INNER JOIN
                         p21_view_apinv_hdr ON pv.po_no = p21_view_apinv_hdr.po_no
WHERE        (p21_view_apinv_hdr.invoice_date = u.invoice_date) AND (pv.po_no = u.po_no)
)t

Open in new window

0
abarefootAuthor Commented:
This is pulling some strange data.  I talk to our purchasing department and it looks like its pulling from the first invoice No from the PO.  POs can have more then one invoice.  So I think we need to look at our joins.  We are currently joining on PO and should be joining on Invoice.

So I'm going to start from square one.  I still need the same thing.  One distinct Item_id per line and that last invoice date and associated invoice no.  

Below is the basic script.

SELECT    Top 200    p21_view_po_hdr.supplier_id, p21_view_apinv_hdr.invoice_no, p21_view_apinv_hdr.invoice_date, p21_view_apinv_hdr.po_no, p21_view_apinv_line.item_id
FROM            p21_view_apinv_hdr INNER JOIN
                         p21_view_apinv_line ON p21_view_apinv_hdr.voucher_no = p21_view_apinv_line.voucher_no INNER JOIN
                         p21_view_po_hdr ON p21_view_apinv_hdr.po_no = p21_view_po_hdr.po_no
WHERE        (p21_view_po_hdr.supplier_id = '101479')

Open in new window


Below is what that script returns

supplier_id                             invoice_no                       invoice_date            po_no                                              item_id
--------------------------------------- -------------------------------- ----------------------- -------------------------------------------------- ----------------------------------------
101479                                  94206263                         2010-04-01 00:00:00.000 1191691                                            SWZ99-0210
101479                                  94204297                         2010-03-25 00:00:00.000 1191691                                            SWZSWHC-70
101479                                  94203530                         2010-03-23 00:00:00.000 1191691                                            SWZ4550U
101479                                  94202809                         2010-03-19 00:00:00.000 1191691                                            SWZ4550U
101479                                  94202809                         2010-03-19 00:00:00.000 1191691                                            SWZSWPC-17
101479                                  94202809                         2010-03-19 00:00:00.000 1191691                                            SWZSWSW-04
101479                                  94202809                         2010-03-19 00:00:00.000 1191691                                            SWZSWSW-11
101479                                  AF03965                          2017-04-28 00:00:00.000 1447780                                            3M18483
101479                                  AF03965                          2017-04-28 00:00:00.000 1447780                                            3M18483
101479                                  AF02662                          2017-04-28 00:00:00.000 1447780                                            3M00048011-05680-7
101479                                  AF02662                          2017-04-28 00:00:00.000 1447780                                            3M00048011-05680-7
101479                                  AF00581                          2017-04-27 00:00:00.000 1446975                                            3MH9A
101479                                  OG67359                          2017-04-27 00:00:00.000 1447267                                            3M8511
101479                                  OG67358                          2017-04-27 00:00:00.000 1447267                                            3M8210
101479                                  OG66971                          2017-04-27 00:00:00.000 1447138                                            3M2091
101479                                  OG66971                          2017-04-27 00:00:00.000 1447138                                            3M2297
101479                                  OG66971                          2017-04-27 00:00:00.000 1447138                                            3M6001
101479                                  OG66971                          2017-04-27 00:00:00.000 1447138                                            3M7502
101479                                  OG68402A                         2017-04-26 00:00:00.000 1447138                                            3M7503
101479                                  OG66929                          2017-04-26 00:00:00.000 1447140                                            3M450-01-01R20
101479                                  OG66929                          2017-04-26 00:00:00.000 1447140                                            3M450-01-01R20
101479                                  OG66929                          2017-04-26 00:00:00.000 1447140                                            3M6001
101479                                  OG66929                          2017-04-26 00:00:00.000 1447140                                            3M6001
101479                                  OG66929                          2017-04-26 00:00:00.000 1447140                                            3M60921
101479                                  OG66929                          2017-04-26 00:00:00.000 1447140                                            3M6878
101479                                  OG66929                          2017-04-26 00:00:00.000 1447140                                            3M6885
101479                                  AG88728                          2017-04-26 00:00:00.000 1447270                                            3M5N11
101479                                  OG67321                          2017-04-26 00:00:00.000 1447263                                            3M2090-72N
101479                                  OG66966                          2017-04-26 00:00:00.000 1447139                                            3M2091
101479                                  OG67276                          2017-04-26 00:00:00.000 1447257                                            3M2091
101479                                  OG67276                          2017-04-26 00:00:00.000 1447257                                            3M2091
101479                                  OG67276                          2017-04-26 00:00:00.000 1447257                                            3M2091
101479                                  OG67348                          2017-04-26 00:00:00.000 1447269                                            3M8210
101479                                  OG67348                          2017-04-26 00:00:00.000 1447269                                            3M8210
101479                                  OG67349                          2017-04-26 00:00:00.000 1447269                                            3M8511
101479                                  OG67349                          2017-04-26 00:00:00.000 1447269                                            3M8511
101479                                  OG67079                          2017-04-26 00:00:00.000 1447175                                            3M2091
101479                                  71599                            2000-02-02 00:00:00.000 1005537                                            3M6900
101479                                  71599                            2000-02-02 00:00:00.000 1005537                                            3M6900
101479                                  71599                            2000-02-02 00:00:00.000 1005537                                            3M8212
101479                                  OG13620                          2000-06-04 00:00:00.000 1016364                                            3M7884
101479                                  OG18898                          2000-06-28 00:00:00.000 1015956                                            3M520-01-17R01
101479                                  OG58654                          2003-11-24 00:00:00.000 1000194                                            3M6900PF
101479                                  OG59028                          2003-11-24 00:00:00.000 1000067                                            3MW-9435-50
101479                                  OG57967                          2003-11-24 00:00:00.000 1000067                                            3M523-01-28
101479                                  OG58822                          2003-11-26 00:00:00.000 1000017                                            3M60926
101479                                  OG58676                          2003-11-24 00:00:00.000 1000069                                            3M12954
101479                                  OG58676                          2003-11-24 00:00:00.000 1000069                                            3M12954
101479                                  OG58676                          2003-11-24 00:00:00.000 1000069                                            3M83695
101479                                  OG58652                          2003-11-24 00:00:00.000 1000194                                            3M2090-1.5
101479                                  OG58820                          2003-11-24 00:00:00.000 1000017                                            3M8550
101479                                  OG58651                          2003-11-26 00:00:00.000 1000194                                            3M2090-3
101479                                  OG30918                          2000-08-26 00:00:00.000 1023455                                            3M8000
101479                                  OG59329                          2003-11-26 00:00:00.000 1002206                                            3M450-01-01R20
101479                                  OG59329                          2003-11-26 00:00:00.000 1002206                                            3M521-01-43R01
101479                                  AW52110                          1976-06-30 00:00:00.000 1412670                                            3MBT-20L
101479                                  AW52110                          1976-06-30 00:00:00.000 1412670                                            3MGVP-1
101479                                  AW52110                          1976-06-30 00:00:00.000 1412670                                            3MGVP-111
101479                                  AW52110                          1976-06-30 00:00:00.000 1412670                                            3MGVP-112
101479                                  AW52110                          1976-06-30 00:00:00.000 1412670                                            3MGVP-443
101479                                  AF88913                          1971-12-14 00:00:00.000 1432352                                            3M2090-36A
101479                                  OG76239                          1950-12-21 00:00:00.000 1390803                                            3M2090-48A-CP
101479                                  OG76239                          1950-12-21 00:00:00.000 1390803                                            3M7093B
101479                                  OG76239                          1950-12-21 00:00:00.000 1390803                                            3M7093B
101479                                  OG76239                          1950-12-21 00:00:00.000 1390803                                            AEA312-1201
101479                                  OG40383                          1950-09-30 00:00:00.000 1057977                                            3M8511
101479                                  OG21706                          1950-07-11 00:00:00.000 1051509                                            3M2091
101479                                  0G61710                          2003-12-08 00:00:00.000 1001258                                            3M2090-2
101479                                  OG61598                          2003-12-09 00:00:00.000 1000902                                            3MSA1500
101479                                  OG61516                          2003-12-09 00:00:00.000 1000902                                            3M24-00-02R01
101479                                  OG61516                          2003-12-09 00:00:00.000 1000902                                            3M520-01-17R01
101479                                  OG61516                          2003-12-09 00:00:00.000 1000902                                            3MGVP-444
101479                                  OG61390                          2003-12-09 00:00:00.000 1001034                                            3M520-03-73
101479                                  OG61388                          2003-12-09 00:00:00.000 1001034                                            3M2090-3
101479                                  OG61388                          2003-12-09 00:00:00.000 1001034                                            3M20BUFFPAD
101479                                  OG61386                          2003-12-09 00:00:00.000 1001034                                            3M8550
101479                                  OG61711                          2003-12-10 00:00:00.000 1001258                                            3M16BUFFPAD
101479                                  OG61709                          2003-12-10 00:00:00.000 1001258                                            3M2090-2
101479                                  OG61648                          2003-12-10 00:00:00.000 1001034                                            3M2090-1.5
101479                                  OG61599                          2003-12-10 00:00:00.000 1000902                                            3M6885
101479                                  OG61518                          2003-12-10 00:00:00.000 1000902                                            3M6001
101479                                  OG61515                          2003-12-10 00:00:00.000 1000902                                            3M2091
101479                                  OG61515                          2003-12-10 00:00:00.000 1000902                                            3M6003
101479                                  OG61515                          2003-12-10 00:00:00.000 1000902                                            3M7093B
101479                                  OG61515                          2003-12-10 00:00:00.000 1000902                                            3M8212
101479                                  OG61515                          2003-12-10 00:00:00.000 1000902                                            3M8511
101479                                  OG61513                          2003-12-10 00:00:00.000 1000902                                            3M12954
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3M2090-2
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3M2091
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3M6003
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3M60923
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3M6300
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3M6900
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3M8511
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3MFT-10
101479                                  OG61389                          2003-12-10 00:00:00.000 1001034                                            3MFT-12
101479                                  OG61336                          2003-12-10 00:00:00.000 1000864                                            3M6200
101479                                  OG61336                          2003-12-10 00:00:00.000 1000864                                            3M6200
101479                                  OG61336                          2003-12-10 00:00:00.000 1000864                                            3M6300
101479                                  OG61336                          2003-12-10 00:00:00.000 1000864                                            3M6300
101479                                  OG61336                          2003-12-10 00:00:00.000 1000864                                            3M8233
101479                                  OG61336                          2003-12-10 00:00:00.000 1000864                                            3M8233
101479                                  78293                            2000-02-04 00:00:00.000 1007788                                            3M60923
101479                                  78293                            2000-02-04 00:00:00.000 1007788                                            3M60923
101479                                  78293                            2000-02-04 00:00:00.000 1007788                                            3M6889
101479                                  78293                            2000-02-04 00:00:00.000 1007788                                            3M8511
101479                                  OG59330                          2003-11-26 00:00:00.000 1002206                                            3M1911
101479                                  OG59440                          2003-11-26 00:00:00.000 1002212                                            3M7093B
101479                                  OG24900                          2000-08-01 00:00:00.000 1052756                                            3M450-01-01R20
101479                                  OG44483                          2003-09-19 00:00:00.000 1006563                                            3M01443
101479                                  OG44483                          2003-09-19 00:00:00.000 1006563                                            3M01442
101479                                  OG44483                          2003-09-19 00:00:00.000 1006563                                            3M01440
101479                                  OG44483                          2003-09-19 00:00:00.000 1006563                                            3M01434
101479                                  0G61519                          2003-12-12 00:00:00.000 1000902                                            3M6895
101479                                  0G61675                          2003-12-12 00:00:00.000 1001242                                            3M450-01-01R20
101479                                  0G62349                          2003-12-12 00:00:00.000 1001474                                            3M6900PF
101479                                  0G62354                          2003-12-12 00:00:00.000 1001538                                            3M8550
101479                                  0G62355                          2003-12-12 00:00:00.000 1001538                                            3M450-01-01R20
101479                                  0G62353                          2003-12-15 00:00:00.000 1001474                                            3M8214
101479                                  0G62348                          2003-12-15 00:00:00.000 1001474                                            3M83695
101479                                  0H62357                          2003-12-16 00:00:00.000 1001538                                            3M6200
101479                                  0H62357                          2003-12-16 00:00:00.000 1001538                                            3M6001
101479                                  0G62714                          2003-12-16 00:00:00.000 1001701                                            3M8550
101479                                  0G62715                          2003-12-16 00:00:00.000 1001701                                            3M520-01-17R01
101479                                  0G62715                          2003-12-16 00:00:00.000 1001701                                            3M520-01-17R01
101479                                  0G62762                          2003-12-16 00:00:00.000 1001724                                            3M6800PF
101479                                  0G62733                          2003-12-17 00:00:00.000 1001677                                            3M2097
101479                                  0G62733                          2003-12-17 00:00:00.000 1001677                                            3M501
101479                                  0G63800                          2003-12-18 00:00:00.000 1002135                                            3M12954
101479                                  0G63800                          2003-12-18 00:00:00.000 1002135                                            3M12954
101479                                  0G63253                          2003-12-18 00:00:00.000 1001703                                            3MFT-31
101479                                  0G63253                          2003-12-18 00:00:00.000 1001703                                            3MFT32
101479                                  0G63253                          2003-12-18 00:00:00.000 1001703                                            3M504
101479                                  0G63253                          2003-12-18 00:00:00.000 1001703                                            3M504
101479                                  0G62356                          2003-12-18 00:00:00.000 1001538                                            3M6003
101479                                  OG59334                          2003-11-26 00:00:00.000 1002212                                            3M520-01-17R01
101479                                  OG58653                          2003-11-26 00:00:00.000 1000194                                            3M6892
101479                                  OG58653                          2003-11-26 00:00:00.000 1000194                                            3M2091
101479                                  OG58653                          2003-11-26 00:00:00.000 1000194                                            3M6885
101479                                  OG58653                          2003-11-26 00:00:00.000 1000194                                            3M6894
101479                                  OG58653                          2003-11-26 00:00:00.000 1000194                                            3M6898
101479                                  OG58653                          2003-11-26 00:00:00.000 1000194                                            3M6898
101479                                  OG58653                          2003-11-26 00:00:00.000 1000194                                            3M7501
101479                                  OG58940                          2003-11-24 00:00:00.000 1002213                                            3M6800PF
101479                                  OG58940                          2003-11-24 00:00:00.000 1002213                                            3M6900PF
101479                                  OG58940                          2003-11-24 00:00:00.000 1002213                                            3M521-01-43R01
101479                                  OG58940                          2003-11-24 00:00:00.000 1002213                                            3M521-01-43R01
101479                                  OG58541                          2003-11-24 00:00:00.000 1002214                                            3M6800PF
101479                                  OG58540                          2003-11-24 00:00:00.000 1002214                                            3M68097
101479                                  OG58540                          2003-11-24 00:00:00.000 1002214                                            3M69097
101479                                  OG58941                          2003-11-25 00:00:00.000 1002213                                            3M2090-2
101479                                  OG59333                          2003-11-25 00:00:00.000 1002212                                            3MCP25WB
101479                                  OG59335                          2003-11-25 00:00:00.000 1002212                                            3M8233
101479                                  OG59335                          2003-11-25 00:00:00.000 1002212                                            3M8233
101479                                  OG59335                          2003-11-25 00:00:00.000 1002212                                            3M60921
101479                                  OG59335                          2003-11-25 00:00:00.000 1002212                                            3M2091
101479                                  OG59919                          2003-12-03 00:00:00.000 1002206                                            3M6900PF
101479                                  OG60687                          2003-12-08 00:00:00.000 1000666                                            3M8000
101479                                  OG60687                          2003-12-08 00:00:00.000 1000666                                            3M8000
101479                                  OG60690                          2003-12-08 00:00:00.000 1000666                                            3MMP12
101479                                  OG61387                          2003-12-08 00:00:00.000 1001034                                            3MMF-99
101479                                  OG61514                          2003-12-08 00:00:00.000 1000902                                            3M8550
101479                                  OG60686                          2003-12-08 00:00:00.000 1000666                                            3M69097
101479                                  OG60686                          2003-12-08 00:00:00.000 1000666                                            3M68097
101479                                  OG60686                          2003-12-08 00:00:00.000 1000666                                            3M6200
101479                                  OG60686                          2003-12-08 00:00:00.000 1000666                                            3M6200
101479                                  OG61046                          2003-12-11 00:00:00.000 1000666                                            3MMP12
101479                                  OG61337                          2003-12-11 00:00:00.000 1000864                                            3M6800PF
101479                                  OG61649                          2003-12-11 00:00:00.000 1001034                                            3MFT-13
101479                                  OG61650                          2003-12-11 00:00:00.000 1001034                                            3MFT-32
101479                                  OG60689                          2003-12-05 00:00:00.000 1000666                                            3M2020-2
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M2097
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M6893
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M7093B
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M7501
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M7502
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M7503
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M8511
101479                                  OG59188                          2003-12-01 00:00:00.000 1000197                                            3M8511
101479                                  OG59614                          2003-12-01 00:00:00.000 1000197                                            3M2090-1
101479                                  OG59474                          2003-12-01 00:00:00.000 1000194                                            3M2020-1
101479                                  OG59474                          2003-12-01 00:00:00.000 1000194                                            3MMF-99
101479                                  OG58821                          2003-12-01 00:00:00.000 1000017                                            3M2090-3
101479                                  OG63936                          2003-12-19 00:00:00.000 1001992                                            3M33
101479                                  0G62955                          2003-12-24 00:00:00.000 1001790                                            YYY3M024-00-02P3
101479                                  0G62955                          2003-12-24 00:00:00.000 1001790                                            YYY3M024-00-02P3
101479                                  OG63609                          2003-12-22 00:00:00.000 1002088                                            3M2090-3
101479                                  OG63581                          2003-12-22 00:00:00.000 1001857                                            3M18485
101479                                  OG63810                          2003-12-22 00:00:00.000 1002130                                            3M2090-1
101479                                  OG63795                          2003-12-22 00:00:00.000 1002118                                            3M7501
101479                                  FG031229                         2003-12-29 00:00:00.000 1002346                                            3M8577
101479                                  OG63596                          2003-12-23 00:00:00.000 1002048                                            3M2090-3
101479                                  OG63252                          2003-12-23 00:00:00.000 1001703                                            3MFT-30
101479                                  OG62352                          2003-12-19 00:00:00.000 1001474                                            3M7502
101479                                  OG63809                          2003-12-23 00:00:00.000 1002130                                            3M520-01-17R01
101479                                  OG63809                          2003-12-23 00:00:00.000 1002130                                            3M6900PF
101479                                  OG64351                          2003-12-23 00:00:00.000 1002130                                            3MMF-48
101479                                  OG64372                          2003-12-23 00:00:00.000 1002365                                            3MSA1500
101479                                  OG64381                          2003-12-23 00:00:00.000 1002370                                            3M450-01-01R20
101479                                  OG63531                          2003-12-19 00:00:00.000 1001992                                            3M5P71

Open in new window

0
Pawan KumarDatabase ExpertCommented:
Please use this -

;WITH CTE AS
(
	SELECT     p21_view_po_hdr.supplier_id, p21_view_apinv_hdr.invoice_no, p21_view_apinv_hdr.invoice_date, 
	p21_view_apinv_hdr.po_no, p21_view_apinv_line.item_id
	FROM            p21_view_apinv_hdr INNER JOIN
							 p21_view_apinv_line ON p21_view_apinv_hdr.voucher_no = p21_view_apinv_line.voucher_no INNER JOIN
							 p21_view_po_hdr ON p21_view_apinv_hdr.po_no = p21_view_po_hdr.po_no
	WHERE (p21_view_po_hdr.supplier_id = '101479')
)
,CTE1 AS
(
	SELECT   MAX(invoice_date) invoice_date , item_id, supplier_id
	FROM CTE        		
	GROUP BY item_id,supplier_id
)
SELECT c.invoice_date , c.item_id , c.supplier_id , t.po_no , t.invoice_no
FROM CTE1 c
CROSS APPLY
(
	SELECT MAX(c1.po_no) po_no , MAX(c1.invoice_no) invoice_no
	FROM CTE c1
	WHERE c1.invoice_date = c.invoice_date AND c1.item_id = c.item_id
	AND c1.supplier_id = c.supplier_id 
)t

Open in new window

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
abarefootAuthor Commented:
I'm reviewing the data tomorrow and will let you know.  Thanks for the help!
0
Pawan KumarDatabase ExpertCommented:
Question abandoned.
Provided solution
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
SQL

From novice to tech pro — start learning today.