Coluna com o saldo atual

Question

Olá a todos, tenho uma tabela com os campos data e valor, preciso exibir o resultado com um coluna saldo, sendo este o valor da linha atual + a linha anterior.

Tentei com o codigo abaixo:

SELECT t1.data, t1.valor, (SELECT SUM(valor) FROM caixa AS t2 WHere t1.data >= t2.data ) AS saldo FROM caixa AS t1 ORDER BY data;

Se a tabela caixa possuir somente um registro por data funciona, a coluna saldo fica certa.

Mas se existir mais de um registro com a mesma data não da certo, o valor da coluna saldo recebe o valor total da data.

Como posso resolver ?

 

Answer 1

Olá Leandro, veja se o exemplo que criei no meu blog te ajuda:

http://emanuelmcdba.spaces.live.com/blog/cns!E634CAFF4AFF243D!683.entry

 

Answer 2

Olá Emanuel,

 

Muito interessante o seu exemplo!

 

Se me permite uma sugestão, você poderia adicionar uma coluna para armazenar a data valor do lançamento, e utilizar essa data (ao invés do código) para fazer o cálculo e ordenação, para que refletisse mais a realidade, já que é muito comum inserirmos lançamentos em atraso (com datas retroativas) ou ainda, lançamentos futuros.

 

Abraços,

Caio Proiete

---

Caio Proiete
http://www.caioproiete.com

Answer 3

Oi Caio, obrigado pela sugestão, concordo com vc.

Eu havia colocado esse exemplo em meu blog após ter ajudado alguem aqui no fórum com essa dúvida, naquele momento o cenário da pessoa era esse, mas irei melhorá-la após sua contribuíção.

 

Valeu.

 

 

Answer 4

Leandro,

 

        SE você utilizar o SQL Server 2005, você pode utilizar uma funcionalidade chamada CTE. Eu peguei um exemplo em algum lugar (Não lembro onde... Se o autor estiver lendo este post, me perdoe por eu não citar a autoria...) que trata de tabela de preços de itens com o preço atual e o preço anterior (Você poderá facilmente modificar para sua necessidade)

 

Code Snippet

CREATE TABLE [dbo].[Itens](

[ItemId] [int] NOT NULL,

[Item] [varchar](100) NOT NULL,

CONSTRAINT [PK_Itens] PRIMARY KEY CLUSTERED

(

[ItemId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

go

 

Insert into Itens (ItemID, Item) Values (1, 'Microcomputador Core 2 Duo')

go

 

CREATE TABLE [dbo].[TabelaPrecos](

[ItemId] [int] NOT NULL,

[DataInicial] [datetime] NOT NULL,

[Preco] [decimal](10, 2) NOT NULL,

CONSTRAINT [PK_TabelaPrecos] PRIMARY KEY CLUSTERED

(

[ItemId] ASC,

[DataInicial] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

go

 

Insert Into TabelaPrecos (ItemId, DataInicial, Preco) Values (1, cast('2008-01-01' as datetime), 100)

Insert Into TabelaPrecos (ItemId, DataInicial, Preco) Values (1, cast('2008-02-01' as datetime), 110)

Insert Into TabelaPrecos (ItemId, DataInicial, Preco) Values (1, cast('2008-03-01' as datetime), 120)

go

 

CREATE VIEW [dbo].[ComparaPreco] AS

WITH ComparaPreco AS

(

SELECT Itens.Item, TabelaPrecos.ItemId, TabelaPrecos.DataInicial, TabelaPrecos.Preco,

ROW_NUMBER() OVER (Partition BY TabelaPrecos.ItemId ORDER BY DataInicial) AS rownum

FROM Itens INNER JOIN TabelaPrecos

ON Itens.ItemId = TabelaPrecos.ItemId)

SELECT RegistroAtual.ItemId, RegistroAtual.Item, RegistroAnterior.Preco AS PrecoAnterior, RegistroAtual.Preco AS PrecoAtual, RegistroAtual.DataInicial AS DataInicial, case when RegistroPosterior.DataInicial is null then cast('2999-12-31 23:59:59' as datetime) else dateadd(s, -1, RegistroPosterior.DataInicial) end AS DataFinal

FROM ComparaPreco RegistroAtual

LEFT JOIN ComparaPreco RegistroPosterior

ON RegistroAtual.rownum = RegistroPosterior.rownum - 1

AND RegistroAtual.ItemId = RegistroPosterior.ItemId

LEFT JOIN ComparaPreco RegistroAnterior

ON RegistroAtual.rownum = RegistroAnterior.rownum + 1

AND RegistroAtual.ItemId = RegistroAnterior.ItemId

go

 

 

Select * from ComparaPreco

go

 

 

 

Answer 5

Bom Dia,

 

Interessante o exemplo e interessante as colocações. Abordei essa questão com profundidade em um Webcast que ministrei conforme o link abaixo:

 

Dicas e Truques sobre consultas complexas no SQL Server

http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!162.entry

 

Embora o link tenha um exemplo resumido, o Webcast trata esse assunto e os problemas comuns com mais detalhes. por hora seguem algumas soluções.

 

Code Snippet

-- Cria a tabela

create table #caixa (idlancamento int identity(1,1),

data smalldatetime, valor smallmoney)

 

-- Insere os registros

insert into #caixa (data, valor) values ('20090101',10.00)

insert into #caixa (data, valor) values ('20090102',15.00)

insert into #caixa (data, valor) values ('20090103',12.00)

insert into #caixa (data, valor) values ('20090104',13.00)

insert into #caixa (data, valor) values ('20090105',18.00)

 

-- Calcula o saldo

select data, valor,

(select sum(valor) from #caixa as t2 where t1.data >= t2.data) as saldo

from #caixa as t1 order by t1.data

 

-- Se houver empate o código não funciona

insert into #caixa (data, valor) values ('20090101',14.00)

select data, valor,

(select sum(valor) from #caixa as t2 where t1.data >= t2.data) as saldo

from #caixa as t1 order by t1.data

 

-- O IDLancamento pode ser utilizado para desempatar (Tie Breaker)

select data, valor,

(select sum(valor) from #caixa as t2 where t1.data >= t2.data and

t1.idlancamento >= t2.idlancamento) as saldo

from #caixa as t1 order by t1.data

 

-- Caso não haja critério para desempatar (Tie Breaker)

-- Você pode criar um (ex: menor lançamento primeiro)

select data, valor,

(select sum(valor) from #caixa as t2 where t1.data >= t2.data and

t1.valor >= t2.valor) as saldo

from #caixa as t1 order by t1.data, t1.valor

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 6

Olá  Emanuel, analisei o código mas o problema que a minha tabela não tem o campo código e por este motivo não tenho uma ordem crescente que não se repete e por isso não obtive sucesso.

Caio, você comentou sobre criar uma coluna  "data valor", esta seria a união da data com o valor do lançamento ? E se existir na mesma data lançamento com o mesmo valor ?

Gustavo, executei os código abaixo, mas não obtive sucesso

select data, valor,
(select sum(valor) from projetadojr as t2 where 
t1.data >= t2.data) as saldo
from projetadojr as t1 order by t1.data

Resultado

http://img204.imageshack.us/img204/9366/imagembf2.png

select data, valor,
(select sum(valor) from projetadojr as t2 where 
t1.data >= t2.data and 
t1.valor >= t2.valor) as saldo
from projetadojr as t1 order by t1.data, t1.valor

Resultado

http://img132.imageshack.us/img132/2798/imagem1at2.png

Como disse acima a tabela que estou efetuando o select não tem um campo código, mas tem um uma chave primaria composta por 3 campos.

Alguma dica retornar este campo saldo ?

Answer 7

Olá Leandro,

 

Tente assim:

 

Code Snippet

declare @t table (data smalldatetime, valor smallmoney)

insert into @t values ('20071001',84000)

insert into @t values ('20071008',800)

insert into @t values ('20071008',200)

insert into @t values ('20071015',78500)

insert into @t values ('20071019',666.67)

insert into @t values ('20071022',200)

insert into @t values ('20071022',300)

insert into @t values ('20071022',675)

insert into @t values ('20071029',179.94)

 

select data, valor,

(select sum(valor) from @t as t2 where

t1.data >= t2.data and t1.valor <= t2.valor) as saldo

from @t as t1 order by t1.data

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 8

Gustavo, deu certo em parte.

Se na mesma data não existir documentos com o mesmo valor da certo, mas se tiver valores iguais na mesma data não da certo.

Answer 9

Olá Leandro,

 

Sempre que existir uma situação de empate o código dará errado. É por isso que é preciso eleger algum critério de "desempate". Anterioremente se a data fosse a mesma "empatava" e o código dava errado. Adicionei o critério de "menor valor". Se a data e o valor forem iguais, então o critério de "menor valor" não será válido, pois, haverá dois valores iguais.

 

Qual seria o critério de desempate no seu caso ?

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 10

Olá Gustavo, com esta explicação compreendi o que ocorre na select.

 

Nos exemplos que você passou tem um campo codigo, sendo este utilizado como desempate.

Na minha tabela não tem este campo, mas tenho a chave primaria, sendo que esta é composta de 5 campos (empresa, filial, emitente, serie, documento)

 

Tentei implementar o where com este campos mas não consegui o resultado esperado.

Da certo de usar estes campos como criterio de desempate ?

 

Answer 11

Olá Leandro,

 

Seu SQL Server é 2005 ?

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 12

Trabalho com sql server 2005, mas a tabela em questão esta em access.

Answer 13

Olá Leandro,

 

No Access ? Agora complicou...

 

Só mesmo se você criar uma função utilizando módulos no Access para fazer esse cálculo. Não me vem em mente uma forma de fazer isso usando só SQL através do suporte do Access.

 

Posso mover a thread ?

 

[ ]s,

 

Gustavo Maia Aguiar

http://gustavomaiaaguiar.spaces.live.com

 

Answer 14

Não precisa mover a thread, a tabela é pequena então vou fazer um select do período, gravar em um temporário com um id e utilizar a solução abaixo que você passou.

-- O IDLancamento pode ser utilizado para desempatar (Tie Breaker)
select data, valor,
(select sum(valor) from #caixa as t2 where t1.data >= t2.data and
t1.idlancamento >= t2.idlancamento) as saldo
from #caixa as t1 order by t1.data


Mas qual seria a solução proposta se a tabela em questão estivesse no Sql Server 2005 ?