CODEWITHSUNDEEP
mysql
Keerthi
Keerthi

Reputation: 11

How to show range of dates as min date and max date for consecutive dates and different range if there is break in sequence

Let's say a tables has exam pass dates of candidates now I want to show start date to end date candidate has passed as range

Table1

Person.              year
11.                    1-1- 2013
11.                     1-1-2014
11.                     1-1-2015
11.                     1-1-2017
11.                     1-1-2018
11.                     1-1-2020

Now I want to sort out the sequence and show like this

11                    1-1-2013  1-1-2015
11.                   1-1-2017  1-1-2018
11.                   1-1-2020

How to do this in sql

Upvotes: 1

Views: 127

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

You can use a query like this to get the result that you want, but its not the best code:

SELECT person, 
    CONCAT (
        MIN( myyear),
         IF( MIN(myyear) = MAX(myyear) ,'', CONCAT('    ',MAX(myyear)) )
    )  AS `year`
FROM (
    SELECT
        e.*
        , @grp := if(YEAR(@last_year)+1 = YEAR(e.myyear), @grp, @grp+1) as grp
        , @last_year := e.myyear as tmp
    FROM exam e
    CROSS JOIN ( SELECT @grp :=0, @ast_year:=0) AS init
    ORDER BY person,myyear
    ) as result
group by grp;


**sample table**

mysql> SELECT * FROM exam;
+----+--------+------------+
| id | person | myyear     |
+----+--------+------------+
|  1 |     11 | 2013-01-01 |
|  2 |     11 | 2014-01-01 |
|  3 |     11 | 2015-01-01 |
|  4 |     11 | 2017-01-01 |
|  5 |     11 | 2018-01-01 |
|  6 |     11 | 2020-01-01 |
+----+--------+------------+
6 rows in set (0,00 sec)

sample query

mysql> SELECT person,
    ->     CONCAT (
    ->         MIN( myyear),
    ->          IF( MIN(myyear) = MAX(myyear) ,'', CONCAT('    ',MAX(myyear)) )
    ->     )  AS `year`
    -> FROM (
    ->     SELECT
    ->         e.*
    ->         , @grp := if(YEAR(@last_year)+1 = YEAR(e.myyear), @grp, @grp+1) as grp
    ->         , @last_year := e.myyear as tmp
    ->     FROM exam e
    ->     CROSS JOIN ( SELECT @grp :=0, @ast_year:=0) AS init
    ->     ORDER BY person,myyear
    ->     ) as result
    -> group by grp;
+--------+--------------------------+
| person | year                     |
+--------+--------------------------+
|     11 | 2013-01-01    2015-01-01 |
|     11 | 2017-01-01    2018-01-01 |
|     11 | 2020-01-01               |
+--------+--------------------------+
3 rows in set (0,00 sec)

mysql>

Upvotes: 1

Related Questions