# 11. 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