We have a database table TBLDATES as follows:
We are trying to group the dates by year and month, and in each group, separate continuous dates with the hyphen and the discontinuous dates with the comma. Below is the desired result:
The result table is ordered by dates which are grouped by year and month. Continuous dates are connected by the hyphen (-) and discontinuous ones are connected by the comma.
SQL in MySQL:
with_counter AS (
SELECT
*
, CASE WHEN LAG(DATES) OVER(PARTITION BY MONTH(DATES) ORDER BY DATES) + 1 < DATES
OR LAG(DATES) OVER(PARTITION BY MONTH(DATES) ORDER BY DATES) IS NULL
THEN 1
ELSE 0
END AS counter
FROM TBLDATES
)
,
with_session AS (
SELECT
*
, SUM(counter) OVER(ORDER BY MONTH(DATES), DAY(DATES)) AS session
FROM with_counter
)
SELECT
CAST(MIN(DAY(DATES)) AS VARCHAR(2)) ||CASE WHEN COUNT(*) = 1
THEN ''
ELSE '-'||CAST(MAX(DAY(DATES)) AS VARCHAR(2))
END
AS daylit
, DAY(MIN(DATES)) AS d
, MONTH(MIN(DATES)) AS mn
, TO_CHAR(MIN(DATES),'Month') AS mth
, YEAR(MIN(DATES)) AS yr
FROM with_session
GROUP BY session
ORDER BY 3,2;
The task is not difficult. We can first group dates by year and month and then dates in each month by whether they are continuous or not (a date is continuous if the result of subtracting the previous date from it is 1, otherwise it isn’t). The dates in August, for instance, can be divided into three groups. The first group contains 8, the second contains 10, 11 and 12, and the third contains 16. Then we find the subgroup containing more than one number (which is the second group for August), join numbers with the hyphen (-), and connect members in the group by comma. The problem is that SQL can only perform equi-grouping by a specific column and that does not support grouping by continuous conditions. The language’s solution is rather tricky by inventing a specific column and performing grouping by it.
It is easy and simple to do it with the open-source esProc SPL:
SPL supports grouping by the continuous conditions. It is convenient to perform such a grouping operation (like this task) with SPL.
Top comments (1)
SPL open source address:github.com/SPLWare/esProc