Ayuda con full outer join

26/08/2009 - 02:37 por Gabrielg | Informe spam
Hola amigos,
Tengo este codigo
select
coalesce(s1.account, s2.account) as Account,
coalesce(s1.balance_001, 0) as Balance_001,
coalesce(s2.balance_002, 0) as Balance_002,
coalesce(s1.AccBalance, s2.AccBalance) as AccBalance
from (
SELECT
001.dbo.JDT1.Account,
SUM (001.dbo.JDT1.SYSDeb - 001.dbo.JDT1.SYSCred) AS Balance_001,
SUM (001.dbo.JDT1.SYSDeb - 001.dbo.JDT1.SYSCred) AS AccBalance
FROM 001.dbo.JDT1
GROUP BY 001.dbo.JDT1.Account
) s1
full outer join (
SELECT
002.dbo.JDT1.Account,
SUM (002.dbo.JDT1.SYSDeb - 002.dbo.JDT1.SYSCred) AS Balance_002,
SUM (002.dbo.JDT1.SYSDeb - 002.dbo.JDT1.SYSCred) AS AccBalance
FROM 002.dbo.JDT1
GROUP BY 002.dbo.JDT1.Account
) s2
on s1.account = s2.account

y obtengo:
Account Balance_001 Balance_002 AccBalance
10002071 573.41 2112.68 573.41
10003013 282.24 0 282.24
10003014 6902.62 1450.72 6902.62

Como veran el campo AccBalance no esta acumulando los saldos de 001 y
002 sino que solo toma el saldo de 001.

Como corrijo esto ?

Muchas gracias

Preguntas similare

Leer las respuestas

#1 Carlos M. Calvelo
26/08/2009 - 12:37 | Informe spam
Hola Gabrielg,

On 26 aug, 02:37, Gabrielg wrote:
Hola amigos,
Tengo este codigo
select
coalesce(s1.account, s2.account) as Account,
coalesce(s1.balance_001, 0) as Balance_001,
coalesce(s2.balance_002, 0) as Balance_002,
coalesce(s1.AccBalance, s2.AccBalance) as AccBalance
from (
SELECT
001.dbo.JDT1.Account,
SUM (001.dbo.JDT1.SYSDeb - 001.dbo.JDT1.SYSCred) AS Balance_001,
SUM (001.dbo.JDT1.SYSDeb - 001.dbo.JDT1.SYSCred) AS AccBalance
FROM 001.dbo.JDT1
GROUP BY 001.dbo.JDT1.Account
) s1
full outer join (
SELECT
002.dbo.JDT1.Account,
SUM (002.dbo.JDT1.SYSDeb - 002.dbo.JDT1.SYSCred) AS Balance_002,
SUM (002.dbo.JDT1.SYSDeb - 002.dbo.JDT1.SYSCred) AS AccBalance
FROM 002.dbo.JDT1
GROUP BY 002.dbo.JDT1.Account
) s2
on s1.account = s2.account

y obtengo:
Account Balance_001 Balance_002 AccBalance
10002071 573.41 2112.68 573.41
10003013 282.24 0 282.24
10003014 6902.62 1450.72 6902.62

Como veran el campo AccBalance no esta acumulando los saldos de 001 y
002 sino que solo toma el saldo de 001.

Como corrijo esto ?




En la cuarta columna del select:
coalesce(s1.AccBalance, s2.AccBalance) as AccBalance
estás eligiendo uno de los balances, cuando tendrías que sumarlos.
Y los AccBalance en los subselect no son necesarios ya que
son lo mismo que Balance_001 y _002. O usas los AccBalance
o los Balance_001 y _002 .

select
coalesce(s1.account, s2.account) as Account,
coalesce(s1.B001, 0) as Balance_001,
coalesce(s2.B002, 0) as Balance_002,
coalesce(s1.B001, 0) + coalesce(s2.B002,0) as AccBalance
from (
SELECT
Account,
SUM (SYSDeb - SYSCred) AS B001
FROM 001.dbo.JDT1
GROUP BY Account
) s1
full outer join (
SELECT
Account,
SUM (SYSDeb - SYSCred) AS B002
FROM 002.dbo.JDT1
GROUP BY Account
) s2
on s1.account = s2.account


También podrías hacerlo con UNION.

Y en el caso de que no necesites Balance_001 y Balance_002 te
quedarías solo con esto:

SELECT
Account,
SUM(SYSDeb - SYSCred) AS Balance
FROM
(
SELECT Account, SYSDeb, SYSCred FROM 001.dbo.JDT1
UNION ALL
SELECT Account, SYSDeb, SYSCred FROM 002.dbo.JDT1
) T
GROUP BY Account


Cuidado con los SUM(SYSDeb - SYSCred). Si uno de los dos campos
es NULL, el resultado de SYSDeb - SYSCred sería null.
Si en las tablas originales estas columnas pueden ser null
hazlo así:

ISNULL(SUM(SYSDeb),0) - ISNULL(SUM(SYSCred),0)

Saludos,
Carlos
email Siga el debate Respuesta Responder a este mensaje
Ads by Google
Help Hacer una preguntaRespuesta Tengo una respuesta
Search Busqueda sugerida