We have a database table TBLDATES as follows:

DATES
2020-08-08
2020-08-10
2020-08-11
2020-08-12
2020-08-16
2020-09-20
2020-09-27
2020-09-28
2020-09-29
2020-09-30
2020-10-01
2020-10-02

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:

year	month	days
2020	8	8,10-12,16
2020	9	20,27-30
2020	10	1-2

 

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:

	A
1	=connect("MYSQL")
2	=A1.query@xi("SELECT * FROM TBLDATES ORDER BY DATES")
3	=A2.group(year(~):year,month(~):month;~.(day(~)).group@i(~-~[-1]!=1).(if(~.len()>1,~.m(1)/"-"/~.m(-1),~.m(1))).concat@c():days)

SPL supports grouping by the continuous conditions. It is convenient to perform such a grouping operation (like this task) with SPL.

 

Q & A Collection

https://stackoverflow.com/questions/64116840/sql-formatting-to-user-friendly-date