Find Out the Employees with the Highest Sales Amount during the Promotional Period

Target

Find out staffs with the highest sales amount in various promotional periods according to the original data in the database.

There are two tables, SaleRecord and Promotion. From Promotion table we can see the start date and end date of every promotion period as below:

We need filter data according to the start and end dates of a certain period, and summarize sales amount by employee as below:

Next, find out and store the record of the employee with the highest sales, and go on with the loop on the Promotion table to get all results as below:

1. esProc solution

You can find that unlike traditional text-based script, esProc script is written in the grid. Traditional scripts usually need to define a lot of variables. Then you must consider what names and types you should define for them and where they should be put and how to find them. While in esProc, these troublesome issues don’t exist at all. The cell names are the natual variable names, and they can be directly referenced in the script without being defined:

A referenced cell name will intelligently change when a row and column is deleted or inserted. E.g, insert a row immediately above B6 (suppose you want to sum the sales) as below:

You can find what should be changed has changed automatically, and what shouldn’t be changed has kept unchanged. The intelligent migration ensures the consistency of the script automatically while you feel free to modify it.

esProc computes from left to right and top to bottom. This operation mode is more convenient than the traditional one in arranging steps and observing relationships between them, making the code neat and tidy even with complex, multi-step computations. It is shown on the pic below:

And the grid style makes natural indents for the script, and it’s especially convenient in writing the subroutine, and looping and branch statement as below:

The indentation naturally forms the loop range, which is more intuitive and simple than the traditional begin… end or {} style.

From another esProc script below, we can see how nested loops, constants, and annotations naturally aligned through the grid cells. One of the reasons that the traditional script is difficult to maintain is the muck-up code. But esProc has no such trouble, as it is shown below.

Next, we will introduce the syntax features of codes mentioned above.

2. TSeq

Firstly, please check the following cells A1, A2, A3:

As you can see:

A1，A2：Execute SQL directly.

A3：Create an empty table to store the future results .

The data type of A1, A2 or A3 is called “TSeq”. It has the following characteristics:

Compatible with SQL result set;

Generic without defining field types;

It eliminates the complexity of the definition process of tables in SQL;

Note: TSeqs are widely used in esProc-style computations.

3. Loop statements

Firstly, let’s look at the pic below:

As you can see:

A4: Define a loop to traverse members of A1.

B5-B8: The loop body formed by natural indents.

“A4” in B5 and B8: The cell where the loop located is the loop variable, which can be accessed directly.

A9: End the indentation and the loop body ends naturally.

Let’s look at the code inside the loop body:

B5: Filter SalesRecord by the start and end dates of Promotion, for example, the second Promotion period is from 2012-11-01 to 2012-11-07:

B6: Group and summarize the result of the previous step, the result is shown below:

B7: Find the maximum value from the previous step:

B8: Add the result of the previous step to A3 as below:

4. Step-by-step computation

Firstly, let’s review the steps through the pic below:

Click cell A2 and check the data of A2 on the right side of the esProc IDE. Then use A2 directly in cell B5, and you can continue to use the results of the previous steps as B6, B7and B8 show. It is called a step-by-step computation.
esProc’s step-by-step operation mode divides a complex object easily into several simple steps.

Therefore, it is particularly suitable for complex database computing.

SQL, however, does not support the step-by-step approach directly. The whole computing process must be written down at a time, and it’s hard to avoid mistakes with the lengthy codes. A stored procedure supports the approach, but it features tedious codes writing, limited deployment, inconvenient migration, complex debugging, and it is difficult to observe intermediate results.

5. Function options

Firstly, let’s see the pic below:

Pay attention to “@i” in B6, which means that the result will include the grouping field clerk_name. It will not include the grouping field without @i.

“@i” is a function option, which is used in esProc to extend the functionalities of the functions.

Similarly, “@1″ means returning the first record of each group; “@o” means direct grouping without sorting. There are only a few function options. They are versatile and easy to grasp, for example, @1 can also be used in “select” function.

“Function + function options” can achieve a large number of functionalities on the basis of a small number of functions.
esProc has advantages of agile syntax and strong expressive ability, which allows users to write codes efficiently.