Convert Excel Power Query to Sap b1 SQL HANA
$10-30 USD
Πληρώθηκε κατά την παράδοση
We have to convert Excel Power Query report to Sap b1 SQL HANA query.
1.
The query will will start with calculating the total sales for each item per year .
--sales per year--
SELECT "ItemCode" , "Year" , SUM("Total") FROM
(SELECT T0."ItemCode" , Extract(year FROM T1."DocDate") AS "Year" , SUM(T0."Quantity") AS "Total"
FROM "CO4"."INV1" T0
LEFT JOIN "CO4"."OINV" T1
ON T0."DocEntry" = T1."DocEntry"
WHERE T1."CANCELED" = 'N'
GROUP BY T0."ItemCode" , Extract(year FROM T1."DocDate")
UNION ALL
SELECT T0."ItemCode" , Extract(year FROM T1."DocDate") AS "Year" , SUM(-T0."Quantity") AS "Total"
FROM "CO4"."RIN1" T0
LEFT JOIN "CO4"."ORIN" T1
ON T0."DocEntry" = T1."DocEntry"
WHERE T1."CANCELED" = 'N'
GROUP BY T0."ItemCode" , Extract(year FROM T1."DocDate"))
GROUP BY "ItemCode" , "Year"
ORDER BY "ItemCode"
--end of sales per year--
Then we want unpivots the result for the last 2 years ( if the current year is in the third quarter will be counted )
Then we want to get the average of monthly sales based on the sales of previous 2 years
( Again if current year is in the third quarter the average will be "sales divided by 12+the current month" else "sales divided by 24)
2.
after that
we need Left join OBTN with OBTQ ON "itemcode" and "sysnumber"
remove Quantity = 0
Group the result by
Item code
Distribution number
expiry date
sum Quantity
3.
then
we need to filter the result
based on a predefined variable "Expiry" representing the number of months we consider the item is expired were
[ExpDate] <= [login to view URL]([login to view URL](), Expiry))
4.
then the result will be grouped and remaining will be only item code and sum of quantity
Group(Item code) : SUM(Quantity)
5.
from [OITM] we take only the active items (forzen for = "N" )
6.
left join OITM with OMRC to get the firm name
7.
left join OITM with OITB to get the group name
8.
Join the result with the table from step 3
9.
the final table
left joint the result from step 8 with the result from step 1
the result will be
8 ItemCode : 8 ItemName : 8 OnHand : 8 IsCommited : 8 OnOrder : 1Near Expiry : 1 monthly_AVG : 1 2021 sales : 1 2022 sales: 8 U_Volume_Code : 8 [login to view URL] : 8 [login to view URL]
10.
add column to check how after how many months each product will be sold lets call it "RMN"
([OnHand]+[OnOrder]-[Near Expiry])/[monthly_AVG]
11.
finally we need to add column calls status
will check if the remaining months is less than a threshold we could define as variable also
" for example 13 months " lets call it "Remaining Months"
=[RMN]<[Remaining Months]
Best Regards
Ταυτότητα Εργασίας: #35364382
