• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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
0
abarefoot
Asked:
abarefoot
  • 16
  • 7
  • 2
1 Solution
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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
Question has a verified solution.

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

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 16
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now