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 month”.

Task: Now we need to add a calculated column TimePeriods: use the starting year/month and ending year/month to calculate a continuous date sequence with an interval of one month, concatenate them with vertical lines, and keep the date format “year M month” unchanged.

Code comparisons:

SQL does not have a direct function for generating date sequences, and usually requires JOIN or recursion to indirectly generate multiple records, making the structure very complex. Some databases do not have aggregation functions for concatenating strings after grouping, making it difficult to generate strings in the specified format even if multiple date sequences of records are generated.

SPL can directly generate date sequences and has powerful methods for processing string sets, which can be easily concatenated into a specified format:

A1： Load data from the database and add a null field called TimePeriods.

A2： Identify the starting year/month and ending year/month in the specified format, generate a date sequence with an interval of one month, convert each member of the sequence into a string in the specified format, and concatenate them with vertical lines. periods()can generate a date sequence, @m represents intervals in month. concat() is used to concatenate a set of strings.

---

esProc SPL is FREE to download and give it a try: esProc SPL FREE Download.