Create "on the fly" records of an SQL query in the interval between 2 dates

Question

Create "on the fly" records of an SQL query in the interval between 2 dates

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I do not know if it is possible, I pose the problem. I want to create records from a SQL query in date range One record per day. From the start date to the end date.

SQL query:

    SELECT  TRFEC1   as fecha_inicio
            ,TRFEC2  as fecha_fin
            ,CODIGO  as codigo
            ,NOMBRE  as cliente
        FROM reservas FORCE INDEX(PRIMARY)
        WHERE TRFEC1>='2018-05-20' AND TRFEC2<='2018-06-10'

From this query I want to create one record per day. 1 for 2018-05-20 , another for 2018-05-21 , other for 2018-05-22 and so on up to 2018-06-10

mysql sql

asked by Ontario.Mbd 31.05.2018 в 21:12

source

2 answers

Cakephp 3: sort main table by sub table field (contain) Error consuming web service of the Tax Agency

score 0 · Answer 1

DECLARE @fechaAIncrementar DATE;
SET @fechaAIncrementar = fecha_inicio;

WHILE @fechaAIncrementar <> @fecha_fin
BEGIN
   //Aqui tu consulta de insercion
   //@fechaAIncrementar es la fecha que vas a guardar para cada registro

   SET @fechaAIncrementar = DATEADD (DAY , 1 , @fechaAIncrementar );
END;

greetings

score 0 · Answer 2

One way is to start from a generated sequence:

SELECT DATE_ADD('2010-05-11', INTERVAL SEQ.NR-1 DAY) 'Fecha'
    FROM (SELECT  @row := @row + 1 as NR
                  FROM (SELECT @row:=0) T
                  CROSS JOIN (SELECT 0  AS N UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1
                  CROSS JOIN (SELECT 0  AS N UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2
                  CROSS JOIN (SELECT 0  AS N UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T3
                  CROSS JOIN (SELECT 0  AS N UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T4
    ) SEQ
    WHERE SEQ.NR - 1  <= DATEDIFF('2018-06-10', '2010-05-11')
    ;

The exit:

|      Fecha |
|------------|
| 2010-05-11 |
| 2010-05-12 |
| 2010-05-13 |
| 2010-05-14 |
...
| 2018-06-08 |
| 2018-06-09 |
| 2018-06-10 |

With the different CROSS JOIN of a query that returns 10 numbers, in this case we generate a sequence of 10 x 10 x 10 x 10 = 10,000 numbers. Then it is simple arithmetic of dates with DATE_ADD() to generate each date.

The only problem with this solution is that you have to initially prefix the limit of the sequence.