asked on
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)
MAX(p21_view_apinv_hdr.inv
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_i
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)
MAX(p21_view_apinv_hdr.inv
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_i
Scripting LanguagesSQL
Last Comment
Pawan Kumar
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
ASKER
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.
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.
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
OUTPUT
SOLUTION 2
OUTPUT
SOLUTION 3
OUTPUT
SOLUTION 4
OUTPUT
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
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)
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
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)
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
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)
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
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)
ASKER
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)
MAX(p21_view_apinv_hdr.inv
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
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_i
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.
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)
MAX(p21_view_apinv_hdr.inv
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
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_i
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.
Can u please show few rows of output u r getting from my query and what u need?
ASKER
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:
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
ASKER
solution 2:
item_id po_no supplier_id invoice_no invoice_date
RAGCLFW25 1232510 120535 4218 2039-12-31 00:00:00.000
ASKER
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
ASKER
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
ASKER
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
Which column(s) are duplicate ? Which solution number needs to be modified.
ASKER
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
Please try this updated..
We just need to Group By and aggregate functions to handle things like this -
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
ASKER
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.
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
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)
ASKER
Kumar,
Can I schedule you so we can get this fixed today?
Can I schedule you so we can get this fixed today?
ASKER
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
ASKER
I'm going to lunch but will be back in an hour if you want to schedule something.
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
ASKER
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'.
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'.
ASKER
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
ASKER
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.
Below is what that script returns
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')
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
I'm reviewing the data tomorrow and will let you know. Thanks for the help!
Question abandoned.
Provided solution
Provided solution
Open in new window