Agora estou tentando colocar esse trecho acima nessa outra query e está dando o seguinte erro:
Mensagem 8120, Nvel 16, Estado 1, Linha 1
Column 'Movimento_Banco.LancamentoID_Ref' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Segue a Query
SELECT
ID,
banco, AGENCIA,
CONTA,
Pagamento, nome,
SUM(ENTRADAS)
AS Entradas,
SUM(SAIDAS)
AS Saidas
FROM
(
SELECT Pagamento
= Case
When (A.DataHoraOld
is null)
Then A.DataHora
Else A.dataHoraOld
End,
Entradas
= (case
when (substring(a.descricao,1,12)
= 'Baixa boleto')
Then (SELECT
movimento.valorpago
FROM
(
SELECT lancamentoid_ref,n_bloquete,sum(valorPago)
as valorpago
FROM
(
SELECT boleto.lancamentoid_ref,b.n_bloquete,valorPago
FROM boletocooperado
b
INNER JOIN
(SELECT
descricao,lancamentoid_ref
FROM movimento_banco)
as boleto
ON
substring(boleto.descricao,17,9)
in (cast(cast(b.n_bloquete
as int)
as char(9)))
UNION
SELECT boleto.lancamentoid_ref,b.n_bloquete,valorPago
FROM boletocooperado
b
INNER JOIN
(SELECT
descricao,lancamentoid_ref
FROM movimento_banco)
as boleto
ON
substring(boleto.descricao,17,8)
in (cast(cast(b.n_bloquete
as int)
as char(8)))
UNION
SELECT boleto.lancamentoid_ref,b.n_bloquete,valorPago
FROM boletocooperado
b
INNER JOIN
(SELECT
descricao,lancamentoid_ref
FROM movimento_banco)
as boleto
ON
substring(boleto.descricao,17,7)
in (cast(cast(b.n_bloquete
as int)
as char(7)))
UNION
SELECT boleto.lancamentoid_ref,b.n_bloquete,valorPago
FROM boletocooperado
b
INNER JOIN
(SELECT
descricao,lancamentoid_ref
FROM movimento_banco)
as boleto
ON
substring(boleto.descricao,17,6)
in (cast(cast(b.n_bloquete
as int)
as char(6)))
UNION
SELECT boleto.lancamentoid_ref,b.n_bloquete,valorPago
FROM boletocooperado
b
INNER JOIN
(SELECT
descricao,lancamentoid_ref
FROM movimento_banco)
as boleto
ON
substring(boleto.descricao,17,5)
in (cast(cast(b.n_bloquete
as int)
as char(5)))
)
as Bloquete
Group by
n_bloquete,lancamentoid_ref
)
Movimento
Where
movimento.lancamentoid_ref
= a.lancamentoid_ref
)
Else (select
a.valor)
end),
0 AS
SAIDAS,
A.Descricao
AS nome,
B.BancoId
AS ID,
B.Nome
AS banco,
B.Agencia
AS AGENCIA,
B.Conta
AS CONTA
FROM
Movimento_Banco A
INNER JOIN
Bancos B ON
A.Bancoid
= B.BancoId
INNER
JOIN FIN_LANCAMENTOSIMPLES
F ON
F.LANCAMENTOID
= A.LANCAMENTOID
WHERE Case
When (F.DataLancamentoOld
is null)
Then F.DataLancamento
Else F.DataLancamentoOld
End >=
'2010-10-19'
AND
Case When (F.DataLancamentoOld
is null)
Then F.DataLancamento
Else F.DataLancamentoOld
End <
'2010-11-25'
AND
(A.Valor
> 0)
AND ISNULL(A.Estornada, 0)
= 0
AND
a.bancoid
= '1'
GROUP BY
B.BancoId,
A.Datahora,A.DataHoraOld,
A.Descricao,
B.Nome,
B.Agencia,
B.Conta,a.valor
UNION SELECT
Pagamento =Case
When (A.DataHoraOld
is null)
Then A.DataHora
Else A.dataHoraOld
End,
0
AS ENTRADAS,
(
SELECT abs(a.valor))
AS SAIDAS,
A.Descricao
AS nome,
B.BancoId
AS ID,
B.Nome
AS banco,
B.Agencia
AS AGENCIA,
B.Conta
AS CONTA
FROM Movimento_Banco
A INNER
JOIN Bancos B
ON A.Bancoid
= B.BancoId
INNER
JOIN FIN_LANCAMENTOSIMPLES
F ON
F.LANCAMENTOID
= A.LANCAMENTOID
WHERE Case
When (F.DataLancamentoOld
is null)
Then F.DataLancamento
Else F.DataLancamentoOld
End >=
'2010-11-19'
AND Case
When (F.DataLancamentoOld
is null)
Then F.DataLancamento
Else F.DataLancamentoOld
End <
'2010-11-25'
AND (A.Valor
< 0)
and ISNULL(A.Estornada, 0)
= 0
AND a.bancoid
= '1'
GROUP BY
B.BancoId,
A.Datahora,A.DataHoraOld,
A.Descricao,
B.Nome,
B.Agencia,
B.Conta,a.valor
)
DERIVEDTBL
GROUP
BY ID,
banco,
AGENCIA, CONTA,
Pagamento,
nome
