How to join two queries in sql for me to total?

Question

How to join two queries in sql for me to total?

Navigation

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

0

Hello friends, I'm here to request your important contribution I have this Script

SELECT 
ISNULL(mes,0)[mes],
ISNULL(Cod_Area,0)[Codigo Area],
ISNULL(nom_emp,0)[Nombre Empleado],
ISNULL(Cod_Puesto,0)[Codigo Puesto],
ISNULL(Puesto,0)[Puesto],
ISNULL ([LENTES],0)[LENTES],
Fecha_Gestion,
ISNULL ([AYUDA ODONTOLOGICA],0)[AYUDA ODONTOLOGICA],
Fecha_Gestion,
ISNULL ([DEFUNCION DE FAMILIA],0)[DEFUNCION DE FAMILIA],
Fecha_Gestion,
ISNULL ([AYUDA MATERNIDAD],0)[AYUDA MATERNIDAD],
Fecha_Gestion,
ISNULL ([AYUDA MEDICAMENTOS (R.L.)],0)[AYUDA MEDICAMENTOS (R.L.)],
 Fecha_Gestion
 FROM
 (SELECT m.mes,tp.Cod_Area,tp.Cod_Puesto,Puesto,     me.nom_emp,TB.Tipo_Benef,Monto,Fecha_Gestion
    FROM Tabla_Principal AS tp
    INNER JOIN Mes AS m ON M.id=TP.mes
    INNER JOIN Tipo_Beneficio AS tb ON TB.Id=TP.Tipo_Benef
    INNER JOIN Maestro_Empl AS me ON me.No_emp = tp.No_emp
    UNION ALL

) AS  TablaDatos
 PIVOT 
(
    SUM (Monto)
    FOR Tipo_Benef IN ([LENTES],[AYUDA ODONTOLOGICA],[DEFUNCION DE FAMILIA],[AYUDA MATERNIDAD],[AYUDA MEDICAMENTOS (R.L.)])
)AS PivotTable
 ;

which results in

Open a way that I can add the total of each one the amount per month example

sql sql-server

asked by norma 10.03.2017 в 17:23

source

2 answers

assignment of values to compound classes C # Decrease the height of the header of a WordPress theme

score 1 · Answer 1

Read a little about Windows Functions from SQL . You can use a SUM something similar to the following:

DECLARE @tabla AS TABLE
( 
    campo1 INT,
    campo2 DATETIME,
    valor money 
)

INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-01-01' , 100);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-01-02' , 100);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-01-03' , 100);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-01-04' , 100);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-02-01' , 150);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-02-02' , 150);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-02-03' , 150);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-02-04' , 150);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-03-01' , 20);
INSERT INTO @tabla (campo1,campo2,valor)VALUES(1,'2017-03-01' , 20);

SELECT
    *,
    SUM(valor) OVER (PARTITION BY MONTH(campo2) ORDER BY campo2) opcion1, --acumulado
    SUM(valor) OVER (PARTITION BY MONTH(campo2) ORDER BY MONTH(campo2) RANGE UNBOUNDED PRECEDING) opcion2 --total del grupo
FROM @tabla

You will have a result like the following:

   campo1         campo2           valor  opcion1 opcion2
 1   1     2017-01-01 00:00:00.000 100.00 100.00  400.00
 2   1     2017-01-02 00:00:00.000 100.00 200.00  400.00
 3   1     2017-01-03 00:00:00.000 100.00 300.00  400.00
 4   1     2017-01-04 00:00:00.000 100.00 400.00  400.00
 5   1     2017-02-01 00:00:00.000 150.00 150.00  600.00
 6   1     2017-02-02 00:00:00.000 150.00 300.00  600.00
 7   1     2017-02-03 00:00:00.000 150.00 450.00  600.00
 8   1     2017-02-04 00:00:00.000 150.00 600.00  600.00
 9   1     2017-03-01 00:00:00.000  20.00  40.00  400.00
10   1     2017-03-01 00:00:00.000  20.00  40.00  400.00

In the opcion1 the column valor accumulates until there is a change of month. In opcion2 , add the amounts of the whole group or the partition made. In this case, per month and each month you have your own subtotal in the opcion2 column.

score 0 · Answer 2

Suggestion:

Insert in a temporary table your main query, to this table add a record_type field, which will have two values: 'd' detail, for the records of your query and then "t" for the totals you need

To the temporary table you insert the totals, simply making a query to the same temporary but grouping by Month, the rest of the field has to go in NULL except the type_registration = 't', if you want it to be more beautiful , to the field [Position] you put 'Total' + Month + ':'

The final result is the same temporary one with the data from your query plus one record per month with the total. To see them sorted, simply an ORDER BY Month, type_registration.

You should have something like this:

mes    Codigo Area N.Empleado Codigo Puesto     LENTES tipo_registro
====== =========== ========== ====== ============ ====== =============
Marzo  999         AAAAAAAAAA 999999 AAAAAAAAAAAA 100.00 d
Marzo  999         BBBBBBBBBB 999999 BBBBBBBBBBBB 200.00 d
Marzo  999         CCCCCCCCCC 999999 CCCCCCCCCCCC  50.00 d
Marzo  NULL        NULL       NULL   Total Marzo: 350.00 t