Hi there

We have a Magento installation ( MySQL ) connected to an ERP platform. Among other information, they exchange stock levels several times per day for each SKU. Either whenever a customer visits a product page in our e-commerce ( Magento checks the stock level in the ERP to ensure there is stock available in case the customer adds the product to the cart ) or every hour, whichever happens first

Magento send the ERP the SKU number. The answer from the ERP can be:

• Stock level - e.g. 3, 5, 25 ( units )
• Zero
• -923 - This means that the product Magento is asking for does not exist in the ERP ( e.g. due to bad coding of product SKU in Magento )

To keep track of this exchange of information, we have created a log file with the following structure ( attached an extract with +60k records, if needed ):

• Log number - sequential 1,2,3,4,5,...
• Date
• Time
• SKU
• Answer from ERP: as mentioned, for example, 25 or 2 or 0 or -923

Each day, there are several checks and replies for a given product ( at least 24 ), so the log file looks something like this:

Log #/Date/Time/SKU/Answer from ERP

235 11/07/2017 14:21:07 SKU1234 1

236 11/07/2017 14:21:08 SKU4567 2

237 11/07/2017 14:21:09 SKU1234 0

238 11/07/2017 14:21:10 SKU7777 -923

239 11/07/2017 14:21:11 SKU1234 0

240 11/07/2017 14:21:12 SKU7777 -923

What we want to create is report in Magento Desktop and a chart with following requirements:

- REPORT: SKU whose last value recorded PER DAY is -923 ( or 0) in each of the last five days- Problem we are having here is that we don't know how to select THE LAST VALUE recorded every day, so we end up with the SUM of the value recorded for every day in the last five days. We have used LAST() instruction but we see it is to select the last day, not the last value in a day

- CHART: Number of SKUs with either stock, zero or error in the last reading for each of the last 10 days, per day - same problem as before

I hope this is clear enough

Happy to provide any further information ( attached extract file from the last few days )

Thanks for your help