Salesperson Achieving the Highest Sales Values during Promotion
l Problem
This is a database problem that occurs in a department store. A database of the department store contains two tables, namely, the Promotion table and the SalesRecord table.
The Promotion table is a calendar for promotion:
The SalesRecord table is used to list the sales for each salesperson in a year:
Please select the salesperson who achieved the highest sales value in each promotion, so as to reward him/her the performance bonus.
l Tip
General steps: Loop through the Promotion table. In each loop, firstly select all the sales records about the current promotion from the SalesRecord table; secondly, group these records by salesperson name and calculate the total sales value for each group so as to search for the salesperson achieving the highest sales value; thirdly, record the corresponding salesperson name in the new table sequence, and then this is the result.
1. Create a resulting table sequence before loops begin;
2. Loop through the records in Promotion table;
3. In each loop body, select all sales records about the current promotion from the SalesRecord table;
4. Group the records by salesperson name, count the total sales value for each salesperson, and create a new table sequence;
5. Select out the sales record in which the salesperson has achieved the highest sales value;
6. Write the salesperson’s name and the current promotion name to the resulting table sequence outside the loop body;
7. After the loops are finished, the resulting table sequence with detail data is what you want.
l Code
|
|
A |
B |
|
|
1 |
=file("C:\\txt\\Promotion.txt").import@t() |
|
The Promotion table |
|
2 |
=file("C:\\txt\\SalesRecord.txt").import@t() |
|
The SalesRecord table |
|
3 |
=create(promo_name,best_sale) |
|
The result table sequence |
|
4 |
for A1 |
|
Loop the Promotion table |
|
5 |
|
=A2.select(sale_date>= A4.start_date && sale_date <=A4.end_date) |
Select all the sales records about the current promotion from the SalesRecord table |
|
6 |
|
=B5.group(clerk_name; ~.sum(sale_amt):total_amt) |
Group the records by clerk_name and count the total sale_amt for each clerk |
|
7 |
|
=B6.maxp(total_amt) |
Search for the clerk who won the highest sale_amt |
|
8 |
|
>A3.insert(0,A4.promo_name,B7.clerk_name) |
Store the clerk_name and the current promo_name in the result table sequence |
|
9 |
=A3 |
|
Answer |
l Result
