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

l  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 the month of June. 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 a table about the remaining stock by the last day of May:

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

 

l  Tip

General steps: 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 the accumulated out-of-stock time. Then, aggregate the total out-of-stock time and the commodity with the longest accumulated out-of-stock time can be found out.

1.  Create a sequence of all dates of the month and align the purchase and sales tables with it. This is to ensure that the purchase table and sales table are aligned completely, so as to avoid the mistake that there are days when no commodities are replenished.

2.  Align the purchase table and sales table with the date sequence. Two sequences grouped by dates will be created. 

3.  Create a table sequence A to record the remaining stocks and the out-of-stock commodities. Table sequence A comprise Commodity, Stock, OosTime, and TotalOosTime fields.

4.  Set the primary key for 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 current commodity in table A and as aggregate its stock changes.

11.  Check if the Stock value of the current commodity turns to 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 and add it to the field of TotalOosTime in table A.

13.  The accumulation for table A is over at the end of the date loop. Get the commodity with the longest TotalOosTime.

 

l  Code

	A	B	C	D
1	=file("C:\\txt\\Stock.txt").import@t().select(month(Datetime)==6)				Stock table of June
2	=file("C:\\txt\\Sale.txt").import@t().select(month(Datetime)==6)				Sales table of June
3	=file("C:\\txt\\Storage.txt").import@t().select(month(Date)==5)				Remaining stocks at the end of May
4	=file("C:\\txt\\Commodity.txt").import@t()				Commodity table
5	'08:00:00		'21:30:00		Business hours of the supermarket
6	=periods@d(date("2009-6-1"), date("2009-6-30"), 1)				Create a sequence with all days of the month
7	=A1.align@a(A6:~,date(Datetime))				Align the purchase table by dates to get the record grouped by dates.
8	=A2.align@a(A6:~,date(Datetime))				Align the sales table by dates once again.
9	=A4.new(ID:Commodity,0:Stock,:OosTime,0:TotalOosTime)				Create a table to get statistics of remaining stocks and out-of-stock commodities.
10	>A9.keys(Commodity)				Set the primary key of the table.
11	=A3.run(A9.find(Commodity).Stock=Stock)				Write the remaining stocks of May to table A as the initial stocks.
12	for A6	=A7(#A12).run(A9.find(Commodity).run(Stock=Stock+Volume))			Loop every day. Summarize commodity purchase volumes in every morning first.
13		=A9.select(Stock<=0).run(OosTime=string(A12)+" "+A5)			Find out the out-of-stock commodities. The opening hour of the supermarket should be recorded as the start point of out-of-stock time.
14		for A8(#A12)	=A9.find(B14.Commodity)		To make it further, loop every sales record of the day.
15			>C14.run(Stock=Stock-B14.Volume)		Accumulate all stocks changes.
16			if C14.Stock<=0	>C14.OosTime=B14.Datetime	If the commodities run out of stock after transactions, write down the current time as the start point of out-of-stock.
17		=A9.select(Stock<=0)			Select all commodities that run out of stock today.
18		>B17.run(TotalOosTime=TotalOosTime+interval@s (OosTime,string(A12)+" "+C5))			Count the out-of-stock time with the closing time of the supermarket as the end point.
19	=A9.maxp(TotalOosTime).Commodity				Select out the commodity with the longest out-of-stock time.

 

l  Result