# * How to Convert Dates in One Group into An Interval in SQL?

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

 CODE1 CODE2 CODE3 RATE VALUE MONTH A B C 1 1 202001 A B C 1 1 202002 A B C 1 1 202003 A B C 2 1 202004 A B C 2 1 202005 A B C 1 1 202006 A B C 1 1 202007 A B C 1 1 202008 A B C 1 1 202009

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:

 CODE1 CODE2 CODE3 RATE VALUE STARTDT ENDDT A B C 1 1 20200101 20200331 A B C 2 1 20200401 20200531 A B C 1 1 20200601 99991230

Oracle SQL:

SELECT

code1,

code2,

code3,

rate,

value,

min(MONTH) start_dt,

CASE

WHEN ROW_NUMBER() OVER(PARTITION BY code1, code2, code3 ORDER BYmax(MONTHDESC) = 1 THEN 99991230

ELSE max(MONTH)

END end_dt

FROM

(

SELECT

t.*,

ROW_NUMBER() OVER(PARTITION BY code1, code2, code3 ORDER BY MONTH) rn1,

ROW_NUMBER() OVER(PARTITION BY code1, code2, code3, rate, value ORDERBY MONTH) rn2

FROM

TBLTEST t

) t

GROUP BY

code1,

code2,

code3,

rate,

value,

rn1 - rn2

ORDER BY

start_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:

 A 1 =connect("oracle") 2 =A1.query@x("SELECT * FROM TBLTEST ORDER BY MONTH") 3 =A2.groups@o(CODE1,CODE2,CODE3,RATE,VALUE;min(MONTH)/"01":STARTDT,string(date((max(MONTH)+1)/"01","yyyyMMdd")-1,"yyyyMMdd"):ENDDT) 4 >A3.m(-1).modify("99991230":ENDDT)

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