Compare the fields of the same column

Question

Compare the fields of the same column

Navigation

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

0

I have the following table:

create table cumpleanios(
   Id_cumpleanios pk ai,
   Id_user mediumint, 
   cumpleanios varchar(12)
);

What I want to do is compare the field cumpleanios to know if a user has already inserted the birthday for the following year, and if not, insert it.

Id_cumpleanios Id_user Cumpleanios
        1         5     14/09/2017
        2         7     28/03/2017
        3         8     08/10/2017
        4         9     03/07/2017
        5         7     28/03/2018

I started a stored procedure and my idea is to send it to call from an event that runs every year (I'll do this also for other tables).

DROP PROCEDURE IF EXISTS InsertCumpleanios;
DELIMITER $$
    CREATE PROCEDURE InsertCumpleanios(IN anioLast varchar(15))
    BEGIN 
    -- Variables donde almacenar lo que nos traemos desde el SELECT             
        DECLARE p_iduser TINYINT;
        DECLARE p_anio varchar(5);
        DECLARE p_mes varchar(3);
        DECLARE p_dia varchar(3);
        DECLARE anioAct varchar(5); 
        DECLARE p_total TINYINT;      
    -- Variable para controlar el fin del bucle
        DECLARE fin TINYINT DEFAULT 0;
    -- El SELECT que vamos a ejecutar
        DECLARE cumple_cursor CURSOR FOR

            SELECT u.Id_user, DAY(c.cumpleanios), MONTH(c.cumpleanios), YEAR(c.cumpleanios)
            FROM cumpleanios c INNER JOIN usuarios u ON u.Id_user = c.Id_user WHERE u.Estado = 'Disponible' ORDER BY YEAR(c.cumpleanios) ASC;

    -- Condición de salida
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET fin=1;       

        OPEN cumple_cursor;
            get_cumple: loop
                FETCH cumple_cursor INTO p_iduser, p_dia, p_mes, p_anio;
                IF fin=1 then
                    LEAVE get_orden;
                END IF;

                SELECT p_iduser, p_dia, p_mes, p_anio;  -- almacena fila de datos actual
                -- SELECT count(DISTINCT campo) FROM tabla
                SET p_total = (SELECT COUNT(DISTINCT p_iduser));

                SELECT YEAR(CURDATE()) as anioActual;   -- Obtiene el año actual

                SELECT u.Id_user, DAY(c.cumpleanios), MONTH(c.cumpleanios), YEAR(c.cumpleanios)
                FROM cumpleanios c INNER JOIN usuarios u ON u.Id_user = c.Id_user 
                WHERE u.Estado = 'Disponible' ORDER BY YEAR(c.cumpleanios) ASC LIMIT p_total;

                IF p_anio < anioActual
                INSERT INTO cumpleanios(Id_user, cumpleanios) VALUES(p_iduser, CONCAT(p_anio, '-', p_mes, '-', p_dia));

           END LOOP get_cumple;
       CLOSE cumple_cursor;

    END $$ DELIMITER ;

mysql stored-procedure

asked by Spoke P 23.08.2017 в 12:28

source

2 answers

Fill local database on Android with records in Firebase How to limit login to a SessionScoped user per session in jsf?

score 0 · Answer 1

I do not understand very well the reason why you need to store the birthday date, per year, of each user, storing the day and month per user and you would have the birthday date (no matter the year), however, as I do not have all the context of his need, any comment would be speculative.

Some tips:

Storing dates as text data type (VARCHAR) is not a recommended practice in the vast majority of cases.
Cursors are useful, but whenever they can be avoided, it is advisable to do so.

I propose an alternative option (without cursor), you can adjust it as needed (evaluate possible performace problems):

mysql> DROP PROCEDURE IF EXISTS 'InsertCumpleanios';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS 'cumpleanios';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS 'cumpleanios' (
    ->   'Id_cumpleanios' SERIAL,
    ->   'Id_user' MEDIUMINT, 
    ->   'cumpleanios' VARCHAR(12)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO 'cumpleanios'
    ->   ('Id_user', 'cumpleanios')
    -> VALUES
    ->   (5, '14/09/2017'),
    ->   (7, '28/03/2017'),
    ->   (8, '08/10/2017'),
    ->   (9, '03/07/2017'),
    ->   (7, '28/03/2018');
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    ->   'Id_cumpleanios',
    ->   'Id_user',
    ->   'cumpleanios'
    -> FROM
    ->   'cumpleanios';
+----------------+---------+-------------+
| Id_cumpleanios | Id_user | cumpleanios |
+----------------+---------+-------------+
|              1 |       5 | 14/09/2017  |
|              2 |       7 | 28/03/2017  |
|              3 |       8 | 08/10/2017  |
|              4 |       9 | 03/07/2017  |
|              5 |       7 | 28/03/2018  |
+----------------+---------+-------------+
5 rows in set (0.00 sec)

mysql> DELIMITER //

mysql> CREATE PROCEDURE 'InsertCumpleanios'(
    ->   IN 'anioLast' VARCHAR(15)
    -> )
    -> BEGIN
    ->   INSERT INTO 'cumpleanios' ('Id_user', 'cumpleanios')
    ->   SELECT
    ->     'c'.'Id_user',
    ->     DATE_FORMAT(
    ->       DATE_ADD(
    ->         STR_TO_DATE('c'.'cumpleanios', '%d/%m/%Y'),
    ->       INTERVAL 1 YEAR),
    ->     '%d/%m/%Y')
    ->   FROM (
    ->     SELECT 'Id_user', COUNT('cumpleanios') 'count'
    ->     FROM 'cumpleanios'
    ->     WHERE YEAR(STR_TO_DATE('cumpleanios', '%d/%m/%Y'))
    ->           IN ('anioLast', 'anioLast' + 1)
    ->     GROUP BY 'Id_user'
    ->     HAVING 'count' = 1
    ->   ) 'der'
    ->     INNER JOIN 'cumpleanios' 'c' ON
    ->                'der'.'Id_user' = 'c'.'Id_user';
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> CALL 'InsertCumpleanios'(YEAR(NOW()));
Query OK, 3 rows affected (0.00 sec)

mysql> SELECT
    ->   'Id_cumpleanios',
    ->   'Id_user',
    ->   'cumpleanios'
    -> FROM
    ->   'cumpleanios';
+----------------+---------+-------------+
| Id_cumpleanios | Id_user | cumpleanios |
+----------------+---------+-------------+
|              1 |       5 | 14/09/2017  |
|              2 |       7 | 28/03/2017  |
|              3 |       8 | 08/10/2017  |
|              4 |       9 | 03/07/2017  |
|              5 |       7 | 28/03/2018  |
|              6 |       5 | 14/09/2018  |
|              7 |       8 | 08/10/2018  |
|              8 |       9 | 03/07/2018  |
+----------------+---------+-------------+
8 rows in set (0.00 sec)

See db-fiddle .

score 0 · Answer 2

The answer of @wchiquito is appropriate to your question, I thought to propose something very similar, but I am going to limit myself to the part of the query to obtain the new dates to insert that if it is different from that answer:

SET @anionuevo = 2018;

INSERT INTO cumpleanios (Id_user, cumpleanios)
select c.id_user,
       STR_TO_DATE(CONCAT(@anionuevo,'-',month(m.cumpleanios),'-',day(m.cumpleanios)), '%Y-%m-%d')
       FROM cumpleanios c
       INNER JOIN (select  Id_user, 
                            MAX(cumpleanios) as cumpleanios
                            from cumpleanios
                            group by Id_user
                   ) m
                   ON m.id_user = c.id_user
                   AND m.cumpleanios = c.cumpleanios
       WHERE YEAR(m.cumpleanios) < @anionuevo;

What do you do?

We obtain the maximum birthday date for each user
Only in the users that have not loaded the date for @anionuevo , that is to say WHERE YEAR(m.cumpleanios) < @anionuevo , we calculate the new date composing it of the day / month and of @anionuevo