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