July 16, 2014 at 7:26 am
Friends, I got the result we needed.
I created this script and it was extremely functional.
Many thanks to all who helped.
Hugs
SELECT
DISTINCT P.ITEMID,
RECENTES.OC,
RECENTES.DIMENSION3_
,(SELECT SUM(P2.QTYORDERED)
FROM PURCHLINE P2
WHERE P2.ITEMID = RECENTES.ITEMID
AND P2.PURCHID = RECENTES.OC
AND P2.DIMENSION3_ = RECENTES.DIMENSION3_) QTD
,(SELECT SUM(I.COSTAMOUNTPOSTED)
FROM INVENTTRANS I
WHERE I.QTY = 0
AND I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.OC) VALOR
FROM PURCHLINE P
INNER JOIN
(SELECT MAX(PL.PURCHID) AS OC, PL.ITEMID, PL.DIMENSION3_
FROM PURCHLINE PL WHERE PL.PURCHSTATUS = '3'
AND EXISTS
(SELECT TOP 1 1 FROM INVENTTRANS I2
WHERE I2.ITEMID = PL.ITEMID
AND I2.QTY = 0
AND I2.TRANSREFID = PL.PURCHID)
GROUP BY PL.ITEMID, PL.DIMENSION3_)RECENTES
ON P.ITEMID = RECENTES.ITEMID
WHERE
P.QTYORDERED > 0
ORDER BY 3
July 16, 2014 at 8:04 am
matfurrier (7/16/2014)
Friends, I got the result we needed.I created this script and it was extremely functional.
Many thanks to all who helped.
Hugs
SELECT
DISTINCT P.ITEMID,
RECENTES.OC,
RECENTES.DIMENSION3_
,(SELECT SUM(P2.QTYORDERED)
FROM PURCHLINE P2
WHERE P2.ITEMID = RECENTES.ITEMID
AND P2.PURCHID = RECENTES.OC
AND P2.DIMENSION3_ = RECENTES.DIMENSION3_) QTD
,(SELECT SUM(I.COSTAMOUNTPOSTED)
FROM INVENTTRANS I
WHERE I.QTY = 0
AND I.ITEMID = RECENTES.ITEMID
AND I.TRANSREFID = RECENTES.OC) VALOR
FROM PURCHLINE P
INNER JOIN
(SELECT MAX(PL.PURCHID) AS OC, PL.ITEMID, PL.DIMENSION3_
FROM PURCHLINE PL WHERE PL.PURCHSTATUS = '3'
AND EXISTS
(SELECT TOP 1 1 FROM INVENTTRANS I2
WHERE I2.ITEMID = PL.ITEMID
AND I2.QTY = 0
AND I2.TRANSREFID = PL.PURCHID)
GROUP BY PL.ITEMID, PL.DIMENSION3_)RECENTES
ON P.ITEMID = RECENTES.ITEMID
WHERE
P.QTYORDERED > 0
ORDER BY 3
Couple of things looking at your code. Your ORDER BY 3 is using an ordinal position to accomplish the sort. Looking at the top level SELECT list it looks like you are sorting on the column RECENTES.DIMENSION3_. You really should use the column name in the ORDER BY clause. If I remember correctly Microsoft has deprecated using the ordinal position in an ORDER BY clause and this functionality could be pulled from a future version of SQL Server. Also, it makes the code more readable.
Also, the EXISTS with a TOP 1, the TOP 1 could probably be pulled out. Test it both ways to see if it makes a change in performance.
Viewing 2 posts - 31 through 31 (of 31 total)
You must be logged in to reply to this topic. Login to reply