#esprocspl Development Tutorials & Tips | Roast Dev

Calculate based on the records within the group and fill the result into the first record — From SQL to SPL #8

Problem description & analysis: The id and nbr of the MS SQL database table are grouping fields. After sorting the records within the group by time field dt, the order of 0 and 1 in the s...

09.03.2025 0 Read More

Get the records after and before the searched one：SQL VS SPL #12

The ProductionLine_Number in a certain table of the Mariadb database is a grouping field, and there are duplicate values in the Cardboard_Number field within the group. Group by ProductionLine_Number,...

10.03.2025 0 Read More

Search for the Closest Matching Record within the Group — From SQL to SPL #9

Problem description & analysis: The table mytable in the MS SQL database has one ConfirmationStarted and multiple Closed statuses for each ID.Task: Now we need to find the record closest ...

12.03.2025 0 Read More

Substring from a Column of Strings — From SQL to SPL #25

Problem description & analysis: The database table tbl has a string field DESCRIPTION.Task: Now we need to retrieve the word ‘EN’ and the subsequent string of numbers from the DESCRIP...

05.05.2025 0 Read More

Calculate Monthly Account Balance and Fill in Missing Dates — From SQL to SPL #10

Problem description & analysis: There is a transaction table for the asset accounts in the MS SQL database, with dates that are not consecutive.Task: Now we need to calculate the balance ...

16.03.2025 0 Read More

Calculate the Hierarchy of Recursive References — From SQL to SPL #11

Problem description & analysis: A certain table in the MS SQL database has a multi-level self-association structure, where the second field parent node ID is a foreign key pointing to the...

18.03.2025 0 Read More

Calculate a Pair of Minimum Values that Meet the Criteria within the Group — From SQL to SPL #12

Problem description & analysis: A table stores events that occur for multiple accounts on multiple dates.Task: Now we need to find a pair of events that meet the criteria under each accou...

23.03.2025 0 Read More

Pair and Transpose Adjacent Records within the Group - From SQL to SPL #13

Problem description & analysis: A certain table stores records of personnel from external sources entering and leaving a building by swiping their cards in recent days. When a person ente...

25.03.2025 0 Read More

Calculate the Number of Intersections between Adjacent Subsets after Grouping — From SQL to SPL #14

Problem description & analysis: A certain database table records the execution status of a project, with multiple people participating in the project every day, and one person can partici...

30.03.2025 0 Read More

Calculate all the year/months included in the year/month interval — From SQL to SPL #15

Problem description & analysis: The second and third fields of a certain database table are the starting year/month, and the ending year/month, respectively, in the format of “year M mo...

01.04.2025 0 Read More