Consulta con un Query

Podrías intentar meter todo en una tabla temporal y luego a grupar por el
campo de salida
SELECT
SBO_001.dbo.JDT1.Account,
sum(SBO_001.dbo.JDT1.SYSDeb - SBO_001.dbo.JDT1.SYSCred) AS
Balance_001,
0 as Balance_004 Into #temp
FROM SBO_001.dbo.OADM, SBO_001.dbo.JDT1 INNER JOIN SBO_001.dbo.OACT ON
SBO_001.dbo.JDT1.Account = SBO_001.dbo.OACT.AcctCode
WHERE SBO_001.dbo.JDT1.Account = 80800005
GROUP BY SBO_001.dbo.JDT1.Account
UNION
SELECT
SBO_004.dbo.JDT1.Account,
0 as Balance_001,
sum(SBO_004.dbo.JDT1.SYSDeb - SBO_004.dbo.JDT1.SYSCred) AS Balance_004
FROM SBO_004.dbo.OADM, SBO_004.dbo.JDT1 INNER JOIN SBO_004.dbo.OACT ON
SBO_004.dbo.JDT1.Account = SBO_004.dbo.OACT.AcctCode
WHERE SBO_004.dbo.JDT1.Account = 80800005
GROUP BY SBO_004.dbo.JDT1.Account

Select Account, sum(Balance_001) as Balance_001 , sum(Balance_004) as
Balance_004
from #temp
group by Account

Otra solución sería hacer todo en una sola consulta, pero tendrías que
revisar cual lento es.
SELECT
SBO_001.dbo.JDT1.Account,
sum(SBO_001.dbo.JDT1.SYSDeb - SBO_001.dbo.JDT1.SYSCred) AS
Balance_001,
(select sum(SBO_004.dbo.JDT1.SYSDeb - SBO_004.dbo.JDT1.SYSCred FROM
SBO_004.dbo.OADM,
SBO_004.dbo.JDT1 INNER JOIN SBO_004.dbo.OACT ON
SBO_004.dbo.JDT1.Account = SBO_004.dbo.OACT.AcctCode
WHERE SBO_004.dbo.JDT1.Account = SBO_001.dbo.JDT1.Account
) AS Balance_004
FROM SBO_001.dbo.OADM, SBO_001.dbo.JDT1 INNER JOIN SBO_001.dbo.OACT ON
SBO_001.dbo.JDT1.Account = SBO_001.dbo.OACT.AcctCode
WHERE SBO_001.dbo.JDT1.Account = 80800005
GROUP BY SBO_001.dbo.JDT1.Account

Espero que te sirva.


"Gabrielg" wrote:

Mostrar la cita

Hola,

Tengo este Query:

SELECT
SBO_001.dbo.JDT1.Account,
sum(SBO_001.dbo.JDT1.SYSDeb - SBO_001.dbo.JDT1.SYSCred) AS
Balance_001,
0 as Balance_004
FROM SBO_001.dbo.OADM, SBO_001.dbo.JDT1 INNER JOIN SBO_001.dbo.OACT ON
SBO_001.dbo.JDT1.Account = SBO_001.dbo.OACT.AcctCode
WHERE SBO_001.dbo.JDT1.Account = 80800005
GROUP BY SBO_001.dbo.JDT1.Account
UNION
SELECT
SBO_004.dbo.JDT1.Account,
0 as Balance_001,
sum(SBO_004.dbo.JDT1.SYSDeb - SBO_004.dbo.JDT1.SYSCred) AS Balance_004
FROM SBO_004.dbo.OADM, SBO_004.dbo.JDT1 INNER JOIN SBO_004.dbo.OACT ON
SBO_004.dbo.JDT1.Account = SBO_004.dbo.OACT.AcctCode
WHERE SBO_004.dbo.JDT1.Account = 80800005
GROUP BY SBO_004.dbo.JDT1.Account

Este es el resultado:
Account Balance_001 Balance_004
80800005 0 -431.67
80800005 590121.07 0

Yo necesito obtener este resultado:
Account Balance_001 Balance_004
80800005 590121.07 -431.67

Como tengo que modificar el Query ?

Muchas Gracias