DEV Community

Judy
Judy

Posted on

How to Display Dates Easily ? #eg9

We have a database table TBLDATES as follows:

Image description

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:

Image description
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;
Enter fullscreen mode Exit fullscreen mode

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:

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

Top comments (1)

Collapse
 
esproc_spl profile image
Judy

SPL open source address:github.com/SPLWare/esProc