cancel
Showing results for 
Search instead for 
Did you mean: 

last sale query for an item

leonardo0482
Explorer
0 Kudos

Good afternoon, I want to know the last sale of each article, I have a query to the inv1 table that I can't find because, for example, an article made 10 sales yesterday and today it had 7 sales, my query brings me 7 Today's sales and I just want the last record of those sales.

my version of sap business one does not have the "DocTime" field

SELECT T0."ItemCode", T0."DocDate", T0."BaseEntry" AS UltimaVenta, T0."Quantity"
FROM INV1 T0
INNER JOIN (
SELECT T1."ItemCode", MAX(T1."DocDate") AS "UltimaVenta"
FROM INV1 T1
GROUP BY T1."ItemCode"
) AS T2
ON T0."ItemCode" = T2."ItemCode" AND T0."DocDate" = T2."UltimaVenta"
ORDER BY T0."DocEntry" DESC

Accepted Solutions (0)

Answers (2)

Answers (2)

Johan_H
Active Contributor

Hi Martínez,

Please give this a try:

select i.ItemCode
,i.ItemName
,i.CreateDate
,isnull((Select Sum(ro.Quantity)
  From INV1 ro
       Inner Join OINV ho On ro.DOcEntry = ho.DocEntry
  Where ho.DocNum = (SELECT MAX(hi.DocNum)
                     FROM INV1 ri
                          INNER JOIN OINV hi ON ri.DocEntry = hi.DocEntry
                     WHERE ri.ItemCode = i.ItemCode)), 0) 
from OITM i

Regards,

Johan

leonardo0482
Explorer
0 Kudos

Thanks for your contribution, but the INV1 table does not have any information field for the time and date of creation. It only has a date.

Johan_H
Active Contributor

Hi Martínez,

Indeed it does not, but we do not need it. The newest invoice will always have the highest DocNum number.

For this purpose I included the OINV table, and as loh mentioned, if you want to see the document time, you can get this field from OINV.

Regards,

Johan

LoHa
Active Contributor
0 Kudos

Hi Martinez,

but the OINV has (CreateDate/CreateTS). In Johann's Query is a join on it (OINV ho).

regards Lothar