Commodities with Longest Accumulated Out-of-Stock Time in a Month

Problem

The tables below are from a simplified supermarket stock control system. To check if the purchase policy is proper, the supermarket needs to find out the commodity with the longest accumulated out-of-stock time in 6 months. The supermarket opens at 8 a.m. and closes at 9:30 p.m. The out-of-stock time of non-business hours will not be counted. The desired table is as follows:





The supermarket replenishes its stock at 5 a.m. every day as shown in the below purchase table which records the volume of every piece of commodity.

The table below is about the remaining stock table by the last day of May:

The table below is a detailed sales record of the supermarket:

Tip

Rough train of thought: Create a table to record the remaining stocks and the out-of-stock commodities first. Group the purchase table and sales table by date and loop every day. Traverse every purchase and sales record to get statistics of out-of-stock commodities and accumulated time of out-of-stock commodities. Then, the total out-of-stock time and the commodity with longest accumulated out-of-stock time can be found out.

1. Create a table sequence to record all dates of the month and align it with the purchase and sales tables. This is to ensure that the purchase table and sales table are aligned completely, so as to avoid the mistake that no commodities had been replenished is shown one day.
2. Align the purchase table and sales table with the date sequence. Two table sequences will be created which are grouped by dates.
3. Create a table sequence A to record the remaining stocks and the out-of-stock commodities. Table sequence A must comprise Commodity, Stock, OosTime, and TotalOosTime fields.
4. Set the primary key of this table as Commodity to facilitate the future searches.
5. Write the remaining stocks of last month to table A as the initial stock.
6. Loop the date sequence of this month, that is, loop each day.
7. In the loop body, retrieve the morning purchase data of this day and add it to table A.
8. Find out the commodities whose stock is 0 in table A. The OosTime is the opening time of the supermarket, that is, 8 a.m.
9. Loop all sales records of the day.
10. In the loop body, find out the commodity in table A and count it as stock changes.
11. Check if the Stock value turns to be 0. If so, then it is out of stock on that day. Write down the OosTime as current time in table A.
12. At the end of the loop, select all out-of-stock commodities of the day in table A. The closing time of the supermarket is the end time. Work out the out-of-stock time point and add it to the field of TotalOosTime in table A.
13. The accumulation of table A is over at the end of the date loop. Choose the commodity with the longest TotalOosTime.

Result