How to Convert Dates in One Group into An Interval

We have a database table TBLTEST. Below is part of its data:

The data is ordered by date. We are trying to group rows by the first five columns, convert dates in each group into an interval, and record the ending date of the last record as the infinite date 99991230. Below is the desired result:

Oracle SQL:

SELECT code1,code2,code3,rate,value,min(MONTH) start_dt,CASEWHENROW_NUMBER()OVER(PARTITIONBYcode1, code2, code3ORDERBYmax(MONTH)DESC) =1THEN99991230ELSEmax(MONTH)ENDend_dtFROM(SELECTt.*,ROW_NUMBER()OVER(PARTITIONBYcode1, code2, code3ORDERBYMONTH) rn1,ROW_NUMBER()OVER(PARTITIONBYcode1, code2, code3, rate, valueORDERBYMONTH) rn2FROMTBLTEST t) tGROUPBYcode1,code2,code3,rate,value,rn1 - rn2ORDERBYstart_dt

It is rather simple to perform the task in the natural way of thinking. We compare neighboring values between rows on the first five columns, and put the current one and the previous row in the same group when values are same, or create a new group if they are different until the last record is compared. As SQL set is unordered, we need to first invent two columns of indexes manually in an extremely complicated way and then perform grouping according to the relationship between the two columns of indexes. You need to be really smart to come up with the solution.

Yet it is easy to write the code using the open-source esProc SPL:

SPL supports ordered sets directly, making it easy to perform grouping when a neighboring value is different.