Consulta total de ventas por mes

ppierce · 22 Septiembre, 2020 22:27

@Willy_Caldero Buenas tarde recurdo a ustede para pedirle la ayuda ya que me han solicitado una consulta donde se muestre las ventas mensuales o sea el total por mes y donde se vaya acumulando este total. ejemplo de la consulta.

MES	               TOTAL Ventas 	              ACUMULADO 
ENERO	            1                               1
FEBRERO	            4                               5
MARZO	            5                               10
ABRIL	            6                               16 
MAYO	            4                               20  
JULIO	            5                               25  
AGOSTO	            10                              35
SEPTIEMBRE	        10	                            45
OCTUBRE		
NOVIEMBRE		
DICIEMBRE

Así musivamente
Ya tenia una consulta parecida lo que me falta es traspasar los meses a filas y los totales a columna y no he logrado. Y pido de su colaboración para realizar dicha consulta. El Codigo de la consutla es siguiente:

/* SELECT FROM [dbo].[OINV] T1 */                          
  DECLARE @fechainicio AS DATE                            
  /* WHERE */                                                
  SET @fechainicio = /* T1.DocDate */'[%1]'

/* SELECT FROM [dbo].[OINV] T2 */                          
  DECLARE @fechafin AS DATE                            
  /* WHERE */                                                
  SET @fechafin = /* T2.DocDate */'[%2]'
;WITH TR as
(
select * from
(
select Month(T0.DocDate) as 'Mes', 
SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns]) as 'TotVta'
FROM OINV T0 
    INNER JOIN OCRD T0C on T0C.CardCode = T0.CardCode
    WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin 
    GROUP BY T0.DocDate
Union all
 select  Month(T1.DocDate) as 'Mes', 
 SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) as 'TotVta'
 FROM ORIN T1 
INNER JOIN OCRD T1C on T1C.CardCode = T1.CardCode
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
GROUP BY T1.DocDate
) as TP
PIVOT (SUM(TP.TotVta) FOR TP.Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pvrt
) 
select   
ISNULL([1],0) AS 'Enero', ISNULL([2],0) as 'Febrero',
 ISNULL([3],0) as 'Marzo', ISNULL([4],0) as 'Abril',
ISNULL([5],0) as 'Mayo', ISNULL([6],0) as 'Junio', 
ISNULL([7],0) as 'Julio', ISNULL([8],0) as 'Agosto',
ISNULL([9],0) as 'Septiembre', ISNULL([10],0) as 'Octubre',
 ISNULL([11],0) as 'Noviembre', ISNULL([12],0) as 'Diciembre',
(ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)
+ISNULL([5],0)+ISNULL([6],0)+ISNULL([7],0)+ISNULL([8],0)
+ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0)) as 'Total'
from  TR

En espera me puedan colaborar con sus conocimiento

Saludos

Willy_Caldero · 23 Septiembre, 2020 14:50

Hola @ppierce, la primera parte:

ppierce:

MES	               TOTAL Ventas 	              ACUMULADO 
ENERO	            1                               1
FEBRERO	            4                               5
MARZO	            5                               10
ABRIL	            6                               16 
MAYO	            4                               20

Si es posible hacerla con el SUM() OVER (), pero si ya lo pones a columna, el PIVOT lo haces para un solo valor, me imagino que esperas tener como resultado, algo así:
Mes => Enero Febrero Marzo … Diciembre
Ventas => 100 200 300 … 1200
Acumulado => 100 300 600 …
Ahí lo que te sugiero es hacer una nueva consulta para la ultima fila y unirla con UNION ALL.
De todas maneras estos cálculos acumulados y totales casi siempre se hacen a nivel del reporteador, sino usa lo del UNION ALL.

ppierce · 23 Septiembre, 2020 17:01

@Willy_Caldero Si esto es lo que deseo pero que los nombre de los mese estén por filas y los valores en columnas

ppierce · 24 Septiembre, 2020 16:56

@Willy_Caldero
buenos dias Ing Willy le pido el favor si me puede orientar como seria la consulta para obtener el acumulado. Para luego realizar la Union All

Es espera de su respuesta

Saludos

Willy_Caldero · 26 Septiembre, 2020 14:27

ppierce:

select   
ISNULL([1],0) AS 'Enero', ISNULL([2],0) as 'Febrero',
 ISNULL([3],0) as 'Marzo', ISNULL([4],0) as 'Abril',
ISNULL([5],0) as 'Mayo', ISNULL([6],0) as 'Junio', 
ISNULL([7],0) as 'Julio', ISNULL([8],0) as 'Agosto',
ISNULL([9],0) as 'Septiembre', ISNULL([10],0) as 'Octubre',
 ISNULL([11],0) as 'Noviembre', ISNULL([12],0) as 'Diciembre',
(ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)
+ISNULL([5],0)+ISNULL([6],0)+ISNULL([7],0)+ISNULL([8],0)
+ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0)) as 'Total'
from  TR

Al final le añades, las subconsultas de acumulados, una subconsulta por cada columna.

UNION ALL
Select
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0,DocDate) <=1)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1,DocDate) <=1)
) as 'Enero',
.....

ppierce · 28 Septiembre, 2020 13:39

@Willy_Caldero
Buenos días
Ing. Caldero, entonces seria doce subconsulta, o sea una para casa mes

Saludos

Willy_Caldero · 28 Septiembre, 2020 14:00

es una consulta, pero fijate que cada columna "Enero’,‘Febrero’,…,‘Diciembre’, es una subconsulta.

ppierce · 28 Septiembre, 2020 15:01

@Willy_Caldero
ing Willy

Cree la consulta de esta mamera y me envía un error de columnas solo coloque una columna para realizar la prueba.

/* SELECT FROM [dbo].[OINV] T1 */                          
  DECLARE @fechainicio AS DATE                            
  /* WHERE */                                                
  SET @fechainicio = /* T1.DocDate */'[%1]'

/* SELECT FROM [dbo].[OINV] T2 */                          
  DECLARE @fechafin AS DATE                            
  /* WHERE */                                                
  SET @fechafin = /* T2.DocDate */'[%2]'



;WITH TR  as
(
select * from
(
/*select T0.CardCode, Month(T0.DocDate) as 'Mes', ((T0.DocTotal-T0.Vatsum-T0.[TotalExpns])-(T0.DocTotal*T0.[DiscPrcnt]/100)) as 'TotVta'*/
select /*T0.CardCode,*/ Month(T0.DocDate) as 'Mes', SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns]) as 'TotVta'
FROM OINV T0 
    INNER JOIN OCRD T0C on T0C.CardCode = T0.CardCode
    /*INNER JOIN OCRG T0G on T0G.GroupCode = T0C.GroupCode and T0G.GroupName = @gname*/
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin GROUP BY T0.DocDate
Union all
/*select T1.CardCode, Month(T1.DocDate) as 'Mes', ((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])-(T1.DocTotal*T1.[DiscPrcnt]/100)*-1) as 'TotVta'*/
select /*T1.CardCode,*/ Month(T1.DocDate) as 'Mes', SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) as 'TotVta'
FROM ORIN T1 
INNER JOIN OCRD T1C on T1C.CardCode = T1.CardCode
/*INNER JOIN OCRG T1G on T1G.GroupCode = T1C.GroupCode and T1G.GroupName = @gname*/
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin GROUP BY T1.DocDate
) as TP
PIVOT (SUM(TP.TotVta) FOR TP.Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pvrt

) 
select 'TOTAL MENSUAL', /*TC.CardCode,TC.CardName,TG.GroupName,*/ 
ISNULL([1],0) AS 'Enero' , ISNULL([2],0) as 'Febrero', ISNULL([3],0) as 'Marzo', ISNULL([4],0) as 'Abril',
ISNULL([5],0) as 'Mayo', ISNULL([6],0) as 'Junio', ISNULL([7],0) as 'Julio', ISNULL([8],0) as 'Agosto',
ISNULL([9],0) as 'Septiembre', ISNULL([10],0) as 'Octubre', ISNULL([11],0) as 'Noviembre', ISNULL([12],0) as 'Diciembre',
(ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)+ISNULL([6],0)+
ISNULL([7],0)+ISNULL([8],0)+ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0)) as 'Total'
from   TR

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=1)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=1)
) as 'Enero'

Willy_Caldero · 28 Septiembre, 2020 15:08

obviamente no te va funcionar, el UNION ALL necesita que ambas consultas tengan la misma cantidad de columnas, debes completar las columnas.

ppierce · 28 Septiembre, 2020 15:22

Disculpe ing. Willy cree la consulta pero me sigue enviando el mismo error

esta es la consulta por favor le pido me pueda ayudar a descrubir donde esta el error

  DECLARE @fechainicio AS DATE                            
  /* WHERE */                                                
  SET @fechainicio = /* T1.DocDate */'[%1]'

/* SELECT FROM [dbo].[OINV] T2 */                          
  DECLARE @fechafin AS DATE                            
  /* WHERE */                                                
  SET @fechafin = /* T2.DocDate */'[%2]'



;WITH TR  as
(
select * from
(
/*select T0.CardCode, Month(T0.DocDate) as 'Mes', ((T0.DocTotal-T0.Vatsum-T0.[TotalExpns])-(T0.DocTotal*T0.[DiscPrcnt]/100)) as 'TotVta'*/
select /*T0.CardCode,*/ Month(T0.DocDate) as 'Mes', SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns]) as 'TotVta'
FROM OINV T0 
    INNER JOIN OCRD T0C on T0C.CardCode = T0.CardCode
    /*INNER JOIN OCRG T0G on T0G.GroupCode = T0C.GroupCode and T0G.GroupName = @gname*/
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin GROUP BY T0.DocDate
Union all
/*select T1.CardCode, Month(T1.DocDate) as 'Mes', ((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])-(T1.DocTotal*T1.[DiscPrcnt]/100)*-1) as 'TotVta'*/
select /*T1.CardCode,*/ Month(T1.DocDate) as 'Mes', SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) as 'TotVta'
FROM ORIN T1 
INNER JOIN OCRD T1C on T1C.CardCode = T1.CardCode
/*INNER JOIN OCRG T1G on T1G.GroupCode = T1C.GroupCode and T1G.GroupName = @gname*/
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin GROUP BY T1.DocDate
) as TP
PIVOT (SUM(TP.TotVta) FOR TP.Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pvrt

) 
select 'TOTAL MENSUAL', /*TC.CardCode,TC.CardName,TG.GroupName,*/ 
ISNULL([1],0) AS 'Enero' , ISNULL([2],0) as 'Febrero', ISNULL([3],0) as 'Marzo', ISNULL([4],0) as 'Abril',
ISNULL([5],0) as 'Mayo', ISNULL([6],0) as 'Junio', ISNULL([7],0) as 'Julio', ISNULL([8],0) as 'Agosto',
ISNULL([9],0) as 'Septiembre', ISNULL([10],0) as 'Octubre', ISNULL([11],0) as 'Noviembre', ISNULL([12],0) as 'Diciembre'
/*(ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)+ISNULL([6],0)+
ISNULL([7],0)+ISNULL([8],0)+ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0)) as 'Total'*/
from   TR

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=1)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=1)
) as 'Enero'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=2)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=2)
) as 'Febrero'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=2)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=2)
) as 'Febrero'
UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=3)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=3)
) as 'marzo'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=4)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=4)
) as 'Abril'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=5)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=5)
) as 'Mayo'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=6)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=6)
) as 'Junio'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=7)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=7)
) as 'Julio'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=8)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=8)
) as 'Agosto'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=9)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=9)
) as 'Septiembre'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=10)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=10)
) as 'Octubre'

UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=11)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=11)
) as 'Noviembre'
UNION ALL

Select (
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate)<=12)
Union all 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=12)
) as 'Dicembre'

Saludos

Willy_Caldero · 28 Septiembre, 2020 22:53

cada columna es una subconsulta, no una nueva consulta, solo debes separarlas por coma, no por UNION ALL.


UNION ALL
Select
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0,DocDate) <=1)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1,DocDate) <=1)
) as 'Enero',
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0,DocDate) <=1)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1,DocDate) <=2)
) as 'Febrero',
.....

ppierce · 29 Septiembre, 2020 14:39

Ing. Willy

Usted me disculpa pero no se que estoy realizando mal y es sobre todo en la subconsulatas que me esta enviando el error. le envio las consulta para pedirle el favor me la revise y me pueda orientar donde estoy comentiendo el erro

sin mas por el momento y esperando me pueda ayudar

/* SELECT FROM [dbo].[OINV] T1 */                          
  DECLARE @fechainicio AS DATE                            
  /* WHERE */                                                
  SET @fechainicio = /* T1.DocDate */'[%1]'

/* SELECT FROM [dbo].[OINV] T2 */                          
  DECLARE @fechafin AS DATE                            
  /* WHERE */                                                
  SET @fechafin = /* T2.DocDate */'[%2]'



;WITH TR  as
(
select * from
(
/*select T0.CardCode, Month(T0.DocDate) as 'Mes', ((T0.DocTotal-T0.Vatsum-T0.[TotalExpns])-(T0.DocTotal*T0.[DiscPrcnt]/100)) as 'TotVta'*/
select /*T0.CardCode,*/ Month(T0.DocDate) as 'Mes', SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns]) as 'TotVta'
FROM OINV T0 
    INNER JOIN OCRD T0C on T0C.CardCode = T0.CardCode
    /*INNER JOIN OCRG T0G on T0G.GroupCode = T0C.GroupCode and T0G.GroupName = @gname*/
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin GROUP BY T0.DocDate
Union all
/*select T1.CardCode, Month(T1.DocDate) as 'Mes', ((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])-(T1.DocTotal*T1.[DiscPrcnt]/100)*-1) as 'TotVta'*/
select /*T1.CardCode,*/ Month(T1.DocDate) as 'Mes', SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) as 'TotVta'
FROM ORIN T1 
INNER JOIN OCRD T1C on T1C.CardCode = T1.CardCode
/*INNER JOIN OCRG T1G on T1G.GroupCode = T1C.GroupCode and T1G.GroupName = @gname*/
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin GROUP BY T1.DocDate
) as TP
PIVOT (SUM(TP.TotVta) FOR TP.Mes IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) pvrt

) 
select  'TOTAL MENSUAL', /*TC.CardCode,TC.CardName,TG.GroupName,*/ 
ISNULL([1],0) AS 'Enero', ISNULL([2],0) as 'Febrero', ISNULL([3],0) as 'Marzo', ISNULL([4],0) as 'Abril',
ISNULL([5],0) as 'Mayo', ISNULL([6],0) as 'Junio', ISNULL([7],0) as 'Julio', ISNULL([8],0) as 'Agosto',
ISNULL([9],0) as 'Septiembre', ISNULL([10],0) as 'Octubre', ISNULL([11],0) as 'Noviembre', ISNULL([12],0) as 'Diciembre',
(ISNULL([1],0)+ISNULL([2],0)+ISNULL([3],0)+ISNULL([4],0)+ISNULL([5],0)+ISNULL([6],0)+
ISNULL([7],0)+ISNULL([8],0)+ISNULL([9],0)+ISNULL([10],0)+ISNULL([11],0)+ISNULL([12],0)) as 'Total'

from   TR
/*inner join OCRD TC on TC.CardCode = TR.CardCode ORDER BY 1*/
/*inner join OCRG TG on TC.GroupCode = TG.GroupCode*/

UNION ALL
Select(
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=1)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=1)
) as 'Enero',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=2)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=2)
) as 'Febrero',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=3)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=3)
) as 'Marzo',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=4)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=4)
) as 'Abril',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=5)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=5)
) as 'Mayo,

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=6)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=6)
) as 'Junio',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=7)
UNION ALL 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=7)
) as 'Julio',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=8)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=8)
) as 'Agosto'

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=9)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=9)
) as 'Septiembre',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=10)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=10)
) as 'Octubre',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=11)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=11)
) as 'Noviembre',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0.DocDate) <=12)
UNION ALL
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1.DocDate) <=12)
) as 'Diciembre'
)

Willy_Caldero · 29 Septiembre, 2020 16:50

Willy_Caldero:

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month (T0,DocDate) <=1)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month (T1,DocDate) <=1)
) as 'Enero'

Fíjate que la subconsulta que te pase, pone un símbolo de + entre facturas y notas de crédito, no se por que las cambiaste por UNON ALL. Revisa tu query y cambia eso.

ppierce · 29 Septiembre, 2020 16:52

@willy si las coloque con el símbolo de + mas y también me da el error

ppierce · 29 Septiembre, 2020 16:59

ppierce:

/* SELECT FROM [dbo].[OINV] T1 */                          
  DECLARE @fechainicio AS DATE                            
  /* WHERE */                                                
  SET @fechainicio = /* T1.DocDate */'[%1]'

/* SELECT FROM [dbo].[OINV] T2 */                          
  DECLARE @fechafin AS DATE                            
  /* WHERE */                                                
  SET @fechafin = /* T2.DocDate */'[%2]'

el erro es este
1). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘+’. 2). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘)’. 3). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘+’. 4). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘)’. 5). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘+’. 6). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘)’. 7). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘+’. 8). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘)’. 9). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘+’. 10). [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ‘)’. 11). [Microsoft][SQL Server Native Client 11.0][SQL Server]Unclosed quotation mark after the character string ’ ) /inner join OCRD TC on TC.CardCode = TR.CardCode ORDER BY 1/ /inner join OCRG TG on TC.GroupCode = TG.GroupCode/'. 12). [Microsoft][SQL Server Native Client 11.0][SQL Server]Statement ‘Valores definidos por usuario’ (CSHS) (s) could not be prepared.

ppierce · 30 Septiembre, 2020 20:22

@Willy_Caldero le pido el favor me puede revisar la consulta que le envió ya que me sigue enviando el error y no he podido descubar cual es a continuación le envió la consulta como la coloque. esta que le muestro es la subconsultas. Espero me puede orientar. a descubrí porque es erro que se da.
Saludos Cordiales

Select(
(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=1)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=1)
 as 'Enero',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=2)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=2)
) as 'FEBRERO',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=3)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=3)
) as 'MARZO',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=4)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=4)
) as 'Abril',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=5)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=5)
) as 'Mayo',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=6)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=6)
) as 'Junio',


(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=7)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=7)
) as 'Julio',


(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=8)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=8)
) as 'Agosto',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=9)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=9)
) as 'Septiembre',


(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=10)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=10)
) as 'Octubre',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=11)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=11)
) as 'Noviembre',

(select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=12)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=12)
) as 'Diciembre',

Gera_Mendez · 30 Septiembre, 2020 22:01

Compañero @ppierce, solo es una detalle de “paréntesis”

/* SELECT FROM [dbo].[OINV] T1 */                          
  DECLARE @fechainicio AS DATE                            
  /* WHERE */                                                
  SET @fechainicio = /* T1.DocDate */---'[%1]'

/* SELECT FROM [dbo].[OINV] T2 */                          
  DECLARE @fechafin AS DATE                            
  /* WHERE */                                                
  SET @fechafin = /* T2.DocDate */---'[%2]'


Select 

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=1)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=1))
 as 'Enero',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=2)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=2)
) as 'FEBRERO',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=3)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=3)
) as 'MARZO',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=4)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=4)
) as 'Abril',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=5)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=5)
) as 'Mayo',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=6)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=6)
) as 'Junio',


((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=7)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=7)
) as 'Julio',


((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=8)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=8)
) as 'Agosto',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=9)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=9)
) as 'Septiembre',


((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=10)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=10)
) as 'Octubre',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=11)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=11)
) as 'Noviembre',

((select SUM(T0.DocTotal-T0.Vatsum-T0.[TotalExpns])
FROM OINV T0 
WHERE T0.CANCELED = 'N' and T0.DocDate between @fechainicio and @fechafin
AND Month(T0.DocDate) <=12)
+ 
(select  SUM((T1.DocTotal-T1.Vatsum-T1.[TotalExpns])*-1) 
 FROM ORIN T1 
WHERE T1.CANCELED = 'N' and T1.DocDate between @fechainicio and @fechafin 
AND Month(T1.DocDate) <=12)
) as 'Diciembre'

Lo he probado y funciona sin problema.

El compañero @Willy_Caldero es un gran consultor y reconozco y admiro la dedicación que tiene para asesorarte de la manera en que lo hace, por ello te sugiero revises a fondo tus consultas y hagas un esfuerzo por analizarlas más de una vez. Recuerda que los que aquí apoyamos no recibimos nada a cambio, no somos retribuidos de manera económica y lo único que nos llevamos es la grata sensación de ayuda al projimo.

Si estás agradecido con el señor @Willy_Caldero, ayúdalo a ayudarte, valora su tiempo y analiza a fondo tus consultas.

Espero tomes de la mejor manera mi comentario, lo hago pensando en ayudarte y hacer más fluida la interacción del foro.

Saludos.

ppierce · 1 Octubre, 2020 14:16

@Gera_Mendez @Willy_Caldero
Desde que he incie en este foro conocí al INg @Willy_Caldero y lo admiro mucho por su forma de explicar y sus conocimiento. Le doy las gracias por todas los conociemineto que he adquiro de su parte. si ya rebise y todo bien gracias.

Solo me resta decirle al Ing. @Willy_Caldero que siga con esa actitud de queres ayudar a todos en especial a los que estamos comenzando en esto y que no tenemos la experiencia y conocimiento que tienes ustedes.

de ante mano muchas gracias ing @Willy_Caldero y todos en este foro que día tras días nos ayudan con nuestra consulta.

system · 8 Octubre, 2020 14:28

Este tema se cerró automáticamente 7 días después de la última publicación. No se permiten nuevas respuestas.