Commodities out of Stock by 5 p.m. for over 10 Days per Month

Problem

The tables below are from a simplified supermarket stock control system. To make sure that the purchasing strategy is proper, the supermarket needs to know the commodities run out of stock by 5 p.m. for over 10 days in a month. The desired tables are as follows:
The table below a commodity list to record info of various commodities.



Below is the purchase record. The supermarket replenishes its stock at 5 o’clock in the morning. The following table records the purchase volumes of each piece of commodity.

Below is a table of remaining stocks at the end of May. It includes all the remaining stocks of the supermarket by the last day of May.

Below is the sales record of the supermarket.

Tip

Rough train of thought: Firstly, merger the sales table and the purchases table, and traverse the table in the order of time. At the same time, make the statistics on the remaining stocks continuously. Check if any commodity runs out of stock at 5 p.m. or when reaching the last record of the day.

1. Create a new table sequence to record the stock input and output (i.e. sales volume and purchase volume, the stock changes). The table will include Datetime, Commodity, and Stock fields.
2. Insert all purchasing records to the new table, including Datetime, Commodity, and Volume fields.
3. Insert all sales record to the new table. Because the sales will cause a decrease in the stocks, the quantity value should be converted to the minus one before inserting them to the stock field.
4. Sort by Datetime.
5. Group by Date to get the daily sales and purchase records of each day.
6. Create a table sequence A to make the real-time statistics on the remaining stocks and times of commodities run out of stocks. The primary key should be set as the commodity field for easier searching.
7. Loop the table of purchase and sales grouped by dates, that is, to loop daily.
8. In the loop body, make further loop for the purchase and sales records of each day.
9. For the commodity in current loop, find out the corresponding record in table A and add the stock change to it.
10. Check if the time of record is after 5.p.m. or it is the last transaction in this day to be settled.
11. If so, start to count the commodities which run out of stock today. Select the commodities whose stock level is 0 from table A. The counter increases by 1 to indicate the first times of out-of-stock. Make a marker in the cell outside of the loop. The marker means that it has been settled on that day to avoid repeated settlement.
12. At the end of all loops, select those commodities which run out of stocks more than 10 times. It is the final result.

Code

Result