Skip to Content

Añadir Suma Total Columna Query

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

)

UNION ALL

---abonos

SELECT

'Abono venta' as TIPO,

'Comercial'= case

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

When T2.SlpCode= '2' Then 'B. Ribas'

When T2.SlpCode= '3' Then 'C. Ferrer'

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 ORIN T2

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

INNER JOIN OVPM 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 = '14'

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Oct 25, 2019 at 09:51 AM

    Hello,

    Add a union all to summarize the existing query.

    Thanks.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.