Restando la fila anterior utilizando la actualización de MySQL.

Answer

1. 

   davy.ai

   July 28, 2023 at 1:08 pm

   Para generar el resultado deseado, se pueden seguir los siguientes pasos:

   1. Agrupar la tabla por mes y nombre, y calcular la suma del monto para cada grupo. Esto se puede lograr utilizando las funciones YEAR() y MONTH() en combinación con GROUP BY.

   SELECT YEAR(date) AS year, MONTH(date) AS month, name, SUM(amount) AS amount
   FROM table_name
   GROUP BY year, month, name
   

   1. Calcular la suma acumulada del monto para cada nombre, utilizando una subconsulta.

   SELECT year, month, name, amount, 
     (SELECT SUM(amount)
      FROM table_name t2
      WHERE t2.name = t1.name AND (t2.year < t1.year OR (t2.year = t1.year AND t2.month <= t1.month))
     ) AS cumulative_sum
   FROM (
     SELECT YEAR(date) AS year, MONTH(date) AS month, name, SUM(amount) AS amount
     FROM table_name
     GROUP BY year, month, name
   ) t1
   

   1. Calcular el incremento o decremento total de la suma acumulada para cada nombre, en comparación con el mes anterior.

   SELECT monthname(date) AS month, name, amount, 
     (cumulative_sum - COALESCE((SELECT cumulative_sum
                                 FROM table_name t2
                                 WHERE t2.name = t1.name AND t2.year = t1.year AND t2.month = t1.month-1), 0)
     ) AS increase_decrease
   FROM (
     SELECT year, month, name, amount, 
       (SELECT SUM(amount)
        FROM table_name t2
        WHERE t2.name = t1.name AND (t2.year < t1.year OR (t2.year = t1.year AND t2.month <= t1.month))
       ) AS cumulative_sum
     FROM (
       SELECT YEAR(date) AS year, MONTH(date) AS month, name, SUM(amount) AS amount
       FROM table_name
       GROUP BY year, month, name
     ) t1
   ) t3
   

   1. Finalmente, filtrar el resultado mostrando solo la última suma acumulada para cada nombre, y ordenar las filas por mes en orden descendente.

   SELECT month, name, amount, increase_decrease
   FROM (
     SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC, month DESC) AS rn
     FROM (
       SELECT monthname(date) AS month, name, amount, 
         (cumulative_sum - COALESCE((SELECT cumulative_sum
                                     FROM table_name t2
                                     WHERE t2.name = t1.name AND t2.year = t1.year AND t2.month = t1.month-1), 0)
         ) AS increase_decrease
       FROM (
         SELECT year, month, name, amount, 
           (SELECT SUM(amount)
            FROM table_name t2
            WHERE t2.name = t1.name AND (t2.year < t1.year OR (t2.year = t1.year AND t2.month <= t1.month))
           ) AS cumulative_sum
         FROM (
           SELECT YEAR(date) AS year, MONTH(date) AS month, name, SUM(amount) AS amount
           FROM table_name
           GROUP BY year, month, name
         ) t1
       ) t3
     ) t4
   ) t5
   WHERE rn = 1
   ORDER BY month DESC
   

   El resultado debería ser:

   Mes	nombre	monto	incremento/decremento total
   Septiembre	john doe	100.00	100.00
   Noviembre	john doe	100.00	0.00
   Diciembre	john doe	20.00	-80.00

Comments are closed.