Añadir Suma Total Columna Query

former_member1103309 · ‎08-28-2019

Hola a tod@s,

Tenemos un informe que se envía por mail y me gustaría incluir en dos columnas, en las mismas, el total de cada una de las columnas.

He visto que pulsando en la cabecera y la tecla Ctrl, lo hace, pero como es un informe automático que va por mail, no me sirve, he buscado información, incluyendo el "sum", antes del campo doctotal, pero no he sabido hacer que funcione....

Os incluyo la consulta por si podéis ayudarme.

Muchas gracias de antemano y un saludo.

SELECT

'Factura venta' as TIPO,

'Comercial'= case

When T2.SlpCode= '1' Then 'A. Ferrer'

When T2.SlpCode= '2' Then 'Bernat R.'

When T2.SlpCode= '3' Then 'Carlos F.'

When T2.SlpCode= '4' Then 'Cati F.'

When T2.SlpCode= '5' Then 'E. Villalba'

When T2.SlpCode= '6' Then 'Jose F.'

When T2.SlpCode= '7' Then 'J. Prats'

When T2.SlpCode= '8' Then 'Jordi F.'

When T2.SlpCode= '9' Then 'Lucas F.'

When T2.SlpCode= '10' Then 'P. López'

When T2.SlpCode= '11' Then 'P. Ruiz'

When T2.SlpCode= '12' Then 'Rosy N.'

When T2.SlpCode= '13' Then 'Tienda'

When T2.SlpCode= '14' Then 'V. Cardona'

When T2.SlpCode= '15' Then 'V. Formentera'

When T2.SlpCode= '16' Then 'D. Perdiguero'

When T2.SlpCode= '17' Then 'J. Diaz'

When T2.SlpCode= '18' Then 'M.J. Cardona'

When T2.SlpCode= '19' Then 'E.Siligato'

When T2.SlpCode= '20' Then 'Julio R.'

When T2.SlpCode= '21' Then 'J.M.Villalba'

When T2.SlpCode= '22' Then 'Juan F. Pérez'

When T2.SlpCode= '23' Then 'Glennys Gomes'end,

isnull(T2.[AgentCode],'') as 'Cobrador',

T2.[DocNum]as 'No. Documento',

T0.[CardCode] as 'Codigo del Cliente',

T0.CardName as 'Nombre del Cliente',

T0.DocNum as 'No. Cobro',

T2.[U_SEIPresC] as 'No. PresuSAC',

T2.[U_SEIPreCT] as 'No. PresuSL',

T2.[Header] as 'Observaciones',

T2.[DocTotal] as 'Total documento',

T1.[SumApplied]as 'Total pago',

SaldoDoc= case

when T2.Doctotal-T1.[SumApplied]= '0' then '0'

when T2.Doctotal-T1.[SumApplied]<> '0' then T2.Doctotal-T2.PaidToDate end,

T2.[DocDate]as 'Fecha documento',

T0.DocDate as 'Fecha Cobro/Pago',

'-'= case

When T3.GroupNum= '1'Thendatediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '9' Then datediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '8' Then datediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '10' Then datediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '11' Then datediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '12' Then datediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '13' Then datediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '14' Then datediff (day,T2.DocDate,T0.DocDate)

When T3.GroupNum= '15' Then datediff (day,T2.DocDate,T0.DocDate)

end

FROM OINV T2

LEFT JOIN RCT2 T1 ON T1.DocEntry = T2.DocEntry

INNER JOIN ORCT T0 ON T0.DocEntry = T1.DocNum

INNER JOIN OCRD T3 ON T0.CardCode = T3.CardCode

WHERE

CAST(GETDATE()-1 AS DATE) = cast(T0.DocDate as date)

and

T1.InvType = '13'

--order by T2.SlpCode

UNION ALL

-- cobros no reconciliados (cobros marcados como "Pago a cuenta") de un día determinado, el día de ejecucion de la consulta.

-- son los que no se aplicaron a una factura y están pendientes de compensar.

select 'Cobros a cuenta' as TIPO, 'Sin comercial' as [Comercial], 'Sin definir' as [Cobrador], T20.[DocNum] as 'No. Documento',

T20.[CardCode] as 'Codigo del Cliente', T20.CardName as 'Nombre del Cliente', T20.DocNum as 'No. Cobro',

--T20.[U_SEIPresC] as 'No. PresuSAC',

--T20.[U_SEIPreCT] as 'No. PresuSL',

'' as 'No. PresuSAC',

'' as 'No. PresuSL',

T20.[Comments] as 'Observaciones',

T20.[DocTotal] as 'Total documento', T20.[DocTotal] as 'Total pago', 0 as [SaldoDoc],

T20.[DocDate] as 'Fecha documento', T20.[DocDate] as 'Fecha Cobro/Pago', 0 as '-'

from ORCT t20

where cast(t20.DocDate as date) = CAST(GETDATE()-1 AS DATE)

and t20.DocEntry not in (

select t30.SrcObjAbs

from ITR1t30

where t30.TransId = t20.TransId and t30.SrcObjAbs = t20.DocEntry

)