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:

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

Una opción es hacerlo con subconsultas, segun pude ver hay un Inner Join que
está haciendo nada:
Es una idea nada mas...
Saludos


SELECT TOP 1 A.Account,
(SELECT sum(SBO_001.dbo.JDT1.SYSDeb - SBO_001.dbo.JDT1.SYSCred) FROM
SBO_001.dbo.JDT1 WHERE SBO_001.dbo.JDT1.Account = A.Account) AS Balance_001,
(SELECT sum(SBO_004.dbo.JDT1.SYSDeb - SBO_004.dbo.JDT1.SYSCred) FROM
SBO_004.dbo.JDT1 WHERE SBO_004.dbo.JDT1.Account = A.Account) AS Balance_004
FROM SBO_001.dbo.JDT1 A
WHERE A.Account€800005


"Gabrielg" escribió en el mensaje de
noticias:

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

Disculpen pero los confundi un poco con el WHERE
SBO_004.dbo.JDT1.Account = 80800005

Lo que yo quiero es unir los saldos de todas las bases de datos y en
el caso de que haya una cuenta que tenga saldo en varias bases me
quede en una sola linea (como se lo represente en el primer post) y en
el caso de que tenga saldo en una sola de las bases me quede en 0. En
total quiero que me aparezcan todas las cuentas que aparecen en ambas
bases.

Muchas gracias por la ayuda!!

En primer lugar, estás metiendo la tabla SBO_001.dbo.OADM en el FROM sin
especificar ningún tipo de combinación.

En segundo lugar, esa consulta no deberías hacerla con un UNION, sino con un
INNER JOIN normal y corriente, combinando por el campo
SBO_004.dbo.JDT1.Account con SBO_001.dbo.JDT1.Account. Algo como (quitando
ya la tabla dbo.OADM):

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

"Caminar sobre el agua y desarrollar software a partir de unas
especificaciones es fácil, si ambas están congeladas."
Edward V. Berard, ingeniero informático

http://blogs.solidq.com/es/elrincondeldba


"Gabrielg" wrote in message
news:

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