I am programming a script in php and I have created the following query:
SELECT t2.id_usuario, t3.nombre, to_char(t1.fichaje_fecha, 'YYYY-MM') AS mes
,SUM(CASE WHEN t1.operacion = '+' THEN t1.tiempo ELSE '00:00:00' END ) AS "positivo"
,SUM(CASE WHEN t1.operacion = '-' THEN t1.tiempo ELSE '00:00:00' END ) AS "negativo"
FROM produccion.bolsa_horas AS t1
INNER JOIN produccion.fichaje AS t2 ON t1.id_fichaje = t2.id_fichaje
INNER JOIN produccion.usuario AS t3 ON t2.id_usuario = t3.id_usuario
WHERE t1.fichaje_fecha > (current_date - INTERVAL '12 months') AND t1.alta = true AND t2.id_usuario = 1
GROUP BY t2.id_usuario, t3.nombre, t3.apellido1, to_char(t1.fichaje_fecha, 'YYYY-MM'), t1.operacion
ORDER BY t2.id_usuario, mes
Whereby you return the following result:
The result that I would like to obtain is in the same line the cumulative of positive and negative monthly:
A bit of the context is that I have a database in which information is kept regarding the signings of the workers and they ask me for a monthly count of their bag of hours (difference between positive and negative balance) for the moment What I want to achieve is that I return in a single line the positive and negative accumulated per month.
The handicap is the table where the hours bag stores positive or negative movements in a single column which can not be grouped by operation.
It escapes my knowledge if you guide me I would appreciate it.
Thank you very much!