Build a table with start and end dates of period

3

Hello!

I have a table with the following structure:

id | fecha_inicio
---+--------------------
10 | 2016-12-01 10:15:00
10 | 2016-12-03 16:02:00
11 | 2016-12-02 11:23:00

I want to build a query that has the beginning and end of the period, where, for example, the end date of the first period of id 10 is 2016-12-03 16:02:00 . The result I'm looking for is something like the following:

id | fecha_inicio        | fecha_final
---+---------------------+--------------------
10 | 2016-12-01 10:15:00 | 2016-12-03 16:02:00
10 | 2016-12-03 16:02:00 | NULL
11 | 2016-12-02 11:23:00 | NULL

Is there a way to build this query using only SQL expressions? I know that it is possible to build it using other languages, but it would be much easier for me to do it directly in the database.

Greetings

    
asked by Barranka 02.01.2017 в 18:07
source

2 answers

2

I was giving a few thoughts to the idea, and I think I found the solution: Use user variables:

  • The first thing I did was sort by id ascending, and by fecha in a way descending , to be able to "drag" the value of fecha .
  • Then, I used a small trick with temporary variables: Since when values are assigned to user variables in a query they are updated in each row , then it is possible to reference their previous value < em> before updating them :
    • An expression case verifies if the id of the current record is the same as the id of the previous record (which is in the variable @id_ant ):
      • If the value of id is equal to the value of @id_ant , then put the value of @f_ini (which is updated in each row with the value of fecha );
      • If the value of id no is equal to the value of @id_ant , then put a NULL .
    • Next, update the values of @id_ant and @f_ini , to be able to use them in the next row.

The result was like this (I used [SQL Fiddle] [ link to make the problem reproducible, I added some more values so that the example was clearer):

SQL Fiddle

MySQL 5.6 Schema Setup :

create table prueba (
    id int unsigned not null,
    fecha datetime
);

insert into prueba values 
(10, '2016-12-01 10:15:00'),
(10, '2016-12-03 16:02:00'),
(10, '2016-12-05 19:19:00'),
(11, '2016-12-02 11:23:00'),
(11, '2016-12-04 22:00:00'),
(12, '2016-12-05 02:00:00');

Query 1 :

-- Contenido original de la tabla:
select * from prueba;

Results :

| id |               fecha |
|----|---------------------|
| 10 | 2016-12-01 10:15:00 |
| 10 | 2016-12-03 16:02:00 |
| 10 | 2016-12-05 19:19:00 |
| 11 | 2016-12-02 11:23:00 |
| 11 | 2016-12-04 22:00:00 |
| 12 | 2016-12-05 02:00:00 |

Query 2 :

-- Consulta original:
select case when @id_ant = id then @f_ini else null end as f_fin
     -- Actualiza el Id y la fecha de inicio
     , @id_ant := id as id
     , @f_ini := fecha as f_ini
from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
order by id asc, fecha desc;

Results :

|               f_fin | id |               f_ini |
|---------------------|----|---------------------|
|              (null) | 10 | 2016-12-05 19:19:00 |
| 2016-12-05 19:19:00 | 10 | 2016-12-03 16:02:00 |
| 2016-12-03 16:02:00 | 10 | 2016-12-01 10:15:00 |
|              (null) | 11 | 2016-12-04 22:00:00 |
| 2016-12-04 22:00:00 | 11 | 2016-12-02 11:23:00 |
|              (null) | 12 | 2016-12-05 02:00:00 |

Query 3 :

-- Consulta con los campos y los valores en el orden correcto:
select id, f_ini, f_fin
from (
        select case when @id_ant = id then @f_ini else null end as f_fin
             -- Actualiza el Id y la fecha de inicio
             , @id_ant := id as id
             , @f_ini := fecha as f_ini
        from (select @id_ant := 0, @f_ini := null, @f_fin := null) as init, prueba
        order by id asc, fecha desc
    ) as a
order by id, f_ini

Results :

| id |               f_ini |               f_fin |
|----|---------------------|---------------------|
| 10 | 2016-12-01 10:15:00 | 2016-12-03 16:02:00 |
| 10 | 2016-12-03 16:02:00 | 2016-12-05 19:19:00 |
| 10 | 2016-12-05 19:19:00 |              (null) |
| 11 | 2016-12-02 11:23:00 | 2016-12-04 22:00:00 |
| 11 | 2016-12-04 22:00:00 |              (null) |
| 12 | 2016-12-05 02:00:00 |              (null) |
    
answered by 02.01.2017 в 18:56
2

It can also be achieved by using MIN() in a sub-query:

select t.id,
       t.fecha_inicio,
       (select min(t2.fecha_inicio)
          from tbl t2
         where t2.id = t.id
           and t2.fecha_inicio > t.fecha_inicio) as fecha_final
  from tbl t
 order by t.id, t.fecha_inicio;

Demo

Demo 2

    
answered by 02.01.2017 в 19:06