Skip to Content
0
Dec 26, 2020 at 12:02 AM

How to query last sales date

467 Views Last edit Dec 26, 2020 at 06:55 AM 2 rev

Hello experts!

I'm using SAP Business One 9.3 version for SAP HANA.

I'm new to SAP and don't have previous knowledge about programming.

I'm trying to query: Item code, item name, last purchase date, last purchase quantity, sales quantity within a period, average sales by day within a period and last sales date.

These are the right values:

But, when I add the OINV and INV1 tables, the values change to these, which are wrong:

The FROM box is written this way when I add the INV1 table :

OITM T0 INNER JOIN OIVL T1 ON T0."ItemCode" = T1."ItemCode" INNER JOIN INV1 T2 ON T0."ItemCode" = T2."ItemCode"

This is what I have that works ok:

SELECT

T0."ItemCode", T0."FrgnName", T0."LastPurDat",

IFNULL(SUM(CASE WHEN T1."DocDate" =T0."LastPurDat" THEN T1."InQty" ELSE 0 END), 0) AS "Last Purchase Qty.",

SUM(CASE WHEN T1."DocDate" >=[%1] THEN T1."OutQty" ELSE 0 END)-SUM(CASE WHEN T1."DocDate" >[%2] THEN T1."OutQty" ELSE 0 END) AS "Period Sales Qty.",

(SUM(CASE WHEN T1."DocDate" >=[%1] THEN T1."OutQty" ELSE 0 END)-SUM(CASE WHEN T1."DocDate" >[%2] THEN T1."OutQty" ELSE 0 END))/(DAYS_BETWEEN (TO_DATE ([%1]), TO_DATE([%2]))) "Day Average Sales of the Period"

FROM

OITM T0 INNER JOIN OIVL T1 ON T0."ItemCode" = T1."ItemCode"

GROUP BY

T0."ItemCode", T0."FrgnName", T0."LastPurDat"

Thanks in advance for all the help!

Best regards.