Ayuda con full outer join

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