DEV Community

Judy
Judy

Posted on

SQL, compute cumulative sums according to the termination condition #eg29

In MSSQL database, data table stores elevator operation records, where turn is the order of a person entering the elevator. The maximum capacity of the elevator is 1,000 kg. There is the capacity limit for the number of passengers it can carry in each stop and the extra passenger(s) need to wait for the next round.

Image description
We want to get the list of the last passengers entering the elevator in all stops.

Image description
Write the following SPL code:

Image description
A1: Retrieve data in SQL and sort data by turn field.

A2: Set initial value for the cumulative variable.

A3: Group data by each elevator stop. When the cumulative value is greater than 1,000, create a new group and reset the cumulative value as the current total passenger weight.

A4: Get the name field of the last record from each group.

Source

SPL open source address

Top comments (1)

Collapse
 
esproc_spl profile image
Judy

Thank you so much for coming!