Consulta ultimo lançamento por mês

Question

Boa tarde,

eu tenho uma tabela de saldo. sempre que existe uma movimentação de alguma conta, ele salva o saldo das contas nessa tabela.

eu quero fazer um select onde ele traga sempre o saldo do ultimo dia do mês. porém eu tenho não posso usar sempre a condição de ultimo dia do mês, pois tem se alguma conta teve movimento no dia 20 por exemplo, so terá saldo gravado neste dia. então para essa conta o comando teria que trazer o saldo deste dia.

não sei se consegui ser claro.

ex.

Resultado do select ficaria

cod    data            saldo

1    30/08/2019    800,00

1    20/09/2019   1200,00

2    31/08/2019   200,00

2    28/09/2019   800,00

Answer 1

Deleted

Answer 2

Lekao87,

O trecho abaixo me deixou um pouco confuso:

"eu quero fazer um select onde ele traga sempre o saldo do ultimo dia do mês. porém eu tenho não posso usar sempre a condição de ultimo dia do mês, pois tem se alguma conta teve movimento no dia 20 por exemplo, so terá saldo gravado neste dia. então para essa conta o comando teria que trazer o saldo deste dia."

Elaborei um exemplo, mas não sei se reflete o que você deseja:

Create Table Saldos
 (Codigo Int,
  Data Date,
  Saldo Float)
Go

Insert Into Saldos 
Values (1, '2019/08/30', 800.00),
		    (1, '2019/09/20', 1200.00),
            (2, '2018/08/31', 200.00),
            (2,  '2019/09/28', 800.00)
Go

Select ROW_NUMBER() Over (Partition By Month(Data) Order By Data Desc) As Ordem,
		   Data,
		  Saldo 
From Saldos
Order By ROW_NUMBER() Over (Partition By Codigo Order By Data Desc) 

Como já foi apresentado um exemplo com CTE, vou demonstrar como seria possível utilizar uma View ou armazernar em outra tabela:

-- Criando um View --
Create View V_UltimoPorMes
As
Select Top 5
           ROW_NUMBER() Over (Partition By Month(Data) Order By Data Desc) As Ordem,
		   Data,
		  Saldo 
From Saldos
Order By ROW_NUMBER() Over (Partition By Codigo Order By Data Desc) 
Go

-- Executando a View --
Select * from V_UltimoPorMes
Where Ordem = 2
Go


-- Armazenando o resultando em outra tabela --
Select ROW_NUMBER() Over (Partition By Month(Data) Order By Data Desc) As Ordem,
		   Data,
		  Saldo 
Into SaldosUltimoDiaDeCadaMes
From Saldos
Order By ROW_NUMBER() Over (Partition By Codigo Order By Data Desc) 
Go

-- Pesquisando os dados --
Select * From SaldosUltimoDiaDeCadaMes
Where Ordem = 2
Go

Ressalto que estes são somente exemplos, formas que temos de tentar implementar a sua necessidade, não sei se estes exemplos te ajudam, não levei em consideração suas regras de negócio, peço que analise, valide e teste.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 3

Valeu jose, fiz e funcionou perfeitamente como precisava

Answer 4

Junior, ve se vc consegue complementar a minha necessidade por favor.

o comando funcionou, porem eu tenho a seguinte situação o saldo ele precisa ser transportado de um mes para o outro.

ex.

seguindo o nosso exemplo

cod    data            saldo

1    30/08/2019    800,00

1    20/09/2019   1200,00

2    31/08/2019   200,00

2    28/09/2019   800,00

3   25/08/2019  250,00

o código 3 não teve movimento no mes 9, consequente a isso ele não gerou saldo para o mes 9, ai ele fica como se no mes 9 tivesse 0 de saldo, porem não é.

é possivel fazer algum comando, ou criar uma tabela temporaria, para que quando um mes não tiver movimentação ele pegue o saldo do mes anterior e grave no mes em questão?

talves pudesse até criar regra pra sempre colocar valor no ultimo dia do mes, tipo a cada mes pegar o o ultimo saldo e gravar como sendo ultimo dia.

obrigado.

Answer 5

Junior, ve se vc consegue complementar a minha necessidade por favor.

o comando funcionou, porem eu tenho a seguinte situação o saldo ele precisa ser transportado de um mes para o outro.

ex.

seguindo o nosso exemplo

cod    data            saldo

1    30/08/2019    800,00

1    20/09/2019   1200,00

2    31/08/2019   200,00

2    28/09/2019   800,00

3   25/08/2019  250,00

o código 3 não teve movimento no mes 9, consequente a isso ele não gerou saldo para o mes 9, ai ele fica como se no mes 9 tivesse 0 de saldo, porem não é.

é possivel fazer algum comando, ou criar uma tabela temporaria, para que quando um mes não tiver movimentação ele pegue o saldo do mes anterior e grave no mes em questão?

talves pudesse até criar regra pra sempre colocar valor no ultimo dia do mes, tipo a cada mes pegar o o ultimo saldo e gravar como sendo ultimo dia.

obrigado.

Lekão87,

Sim. Podemos pensar nesta possibilidade, vou analisar o seu cenário.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 6

Bom dia Jose, pedi ajuda ao Junior, mas se vc puder também ajudar.

ve se vc consegue complementar a minha necessidade por favor.

o comando funcionou, porem eu tenho a seguinte situação o saldo ele precisa ser transportado de um mes para o outro.

ex.

seguindo o nosso exemplo

cod    data            saldo

1    30/08/2019    800,00

1    20/09/2019   1200,00

2    31/08/2019   200,00

2    28/09/2019   800,00

3   25/08/2019  250,00

o código 3 não teve movimento no mes 9, consequente a isso ele não gerou saldo para o mes 9, ai ele fica como se no mes 9 tivesse 0 de saldo, porem não é.

é possivel fazer algum comando, ou criar uma tabela temporaria, para que quando um mes não tiver movimentação ele pegue o saldo do mes anterior e grave no mes em questão?

talves pudesse até criar regra pra sempre colocar valor no ultimo dia do mes, tipo a cada mes pegar o o ultimo saldo e gravar como sendo ultimo dia.

obrigado.

Answer 7

Junior, ve se vc consegue complementar a minha necessidade por favor.

o comando funcionou, porem eu tenho a seguinte situação o saldo ele precisa ser transportado de um mes para o outro.

ex.

seguindo o nosso exemplo

cod    data            saldo

1    30/08/2019    800,00

1    20/09/2019   1200,00

2    31/08/2019   200,00

2    28/09/2019   800,00

3   25/08/2019  250,00

o código 3 não teve movimento no mes 9, consequente a isso ele não gerou saldo para o mes 9, ai ele fica como se no mes 9 tivesse 0 de saldo, porem não é.

é possivel fazer algum comando, ou criar uma tabela temporaria, para que quando um mes não tiver movimentação ele pegue o saldo do mes anterior e grave no mes em questão?

talves pudesse até criar regra pra sempre colocar valor no ultimo dia do mes, tipo a cada mes pegar o o ultimo saldo e gravar como sendo ultimo dia.

obrigado.

Lekão87,

Sim. Podemos pensar nesta possibilidade, vou analisar o seu cenário.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Lekão87,

Você possui alguma tabela específica que armazene a movimentação mensal, ou tem uma tabela que armazena somente o resultado dos dados?

Pergunto isso, para entendermos justamente como seria possível trabalhar com esta transferência de saldos.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 8

Deleted

Answer 9

Sim, eu tenho uma tabela MV que é de movimento.

select 
	idmovimento
	,DtLancamento
	,idconta as debito
	,idcontrapartida as credito
	,VlLancamento 
from 
	wcont.mv00269

a movimentação dessa tabela gera saldo na tabela SD

select 
	idsaldo
	,idconta
	,dtsaldo
	,vlcredito
	,vldebito
from 
	wcont.sd00269

Answer 10

Deleted

Answer 11

Sim, eu tenho uma tabela MV que é de movimento.

select 
	idmovimento
	,DtLancamento
	,idconta as debito
	,idcontrapartida as credito
	,VlLancamento 
from 
	wcont.mv00269

a movimentação dessa tabela gera saldo na tabela SD

select 
	idsaldo
	,idconta
	,dtsaldo
	,vlcredito
	,vldebito
from 
	wcont.sd00269

Lekão87,

Neste caso, então teriamos que fazer uma verificação, na tabela de Saldos com base no Código do movimento e data de saldo?

Pois se tabela de Movimento temos a data de movimentação, então seria uma comparação entre data de movimento e data de lançamento do saldo!

É isso? Caso venha a ser, talvez o uso do comando Merge envolvendo estas duas tabelas possa ajudar.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 12

José, vc é o cara mano.

muitoooo obrigadoooo.

ficou perfeito.

Answer 13

Deleted

Answer 14

Lekão87,

Sei que meu exemplo não chega aos pés do que foi apresentado pelo José Diz, mas quebrei a minha cabeça para tentar chegar em uma conclusão, não sei se era isso que você queria mais tentei.

Veja abaixo:

Create Table Saldos
 (Codigo Int,
  Data Date,
  Saldo Float)
Go

Insert Into Saldos 
Values (1, '2019/08/30',  800),
            (1, '2019/09/20',  1200),
            (2, '2019/08/31',  200),
            (2, '2019/09/28',  800),
            (3,  '2019/08/25', 250)
Go

Select * From Saldos
Go

-- Definindo a Maior Data Por Codigo --
;With CTEMaiorDataPorCodigo (Codigo, MaiorData)
As
(
 Select Codigo, Max(Data) As MaiorData From Saldos
 Group By Codigo
),
-- Definindo o último Saldo Por Codigo --
CTEUltimoSaldoPorCodigo (Codigo, Data, UltimoSaldo)
As
(
 Select S.Codigo, S.Data, LAST_VALUE(S.Saldo) Over (Partition By S.Codigo Order By S.Data) As UltimoSaldo
 From Saldos S Inner Join CTEMaiorDataPorCodigo C
                                          On C.Codigo = S.Codigo
										  And C.MaiorData = S.Data
 Group By S.Codigo, S.Data, S.Saldo
),
-- Definindo a Faixa de Datas --
CTEFaixasDeDatas (Codigo, DataInicial, DataFinal)
As
(
 Select Codigo, Min(Data), Max(Data) From Saldos                                                                         
 Group By Codigo
)
-- Trazendo os resultados --
Select C.Codigo, 
           Case 
		   When (CT.DataInicial < CT.DataFinal) Then CT.DataFinal
		   Else DateAdd(Month,1,CT.DataInicial)
		   End As 'Mês de Transporte',
		   CU.UltimoSaldo
From CTEMaiorDataPorCodigo C Inner Join CTEUltimoSaldoPorCodigo CU
                         	  On CU.Codigo = C.Codigo
													 Inner Join CTEFaixasDeDatas CT
													  On CT.Codigo = CU.Codigo
**Aplique os wheres que entender necessários													  

Levei em co nsideração a pequena porção de dados que você apresentou anteriormente, obtive este resultado:

Não levei em consideração o nome de suas tabelas, campos, regras de negócio, como destaquei levei em consideração o que fomos evoluíndo no entendimento do post.

Você pode aproveitar este esboço de código, evoluíndo o mesmo para um View ou Stored Procedure, inclusive o resultado poderá ser direcionado para uma outra tabela ou até mesmo se trabalhar diretamente com a cláusula Output, caso venha a ser necessário.

Peço desculpas pela demora, estou em aula e fui elaborando os códigos aos pouquinhos.

Fico feliz por ter conseguido chegar próximo ao que você necessita, é um grande aprendizado o que fazemos aqui nos fóruns.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 15

kkk, manda a conta ai que a gente da um jeito.kkkk

rapaz, hoje me apareceu mais uma novidade.

que que acontece. a minha tabela de saldo é por empresa.

SD00269, SD00030, SD00531 ...

hoje foi gerar o código #4 para a empresa 30. e apresentou um erro.

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Segue o cod usado.

-- código #4
--
IF Object_ID ('tempDB..#SaldoMes', 'U') is not null
  DROP TABLE #SaldoMes;

;with 
-- gera sequência para encontrar último saldo de cada mês/ano
tbSaldo_seq as (
SELECT s.idconta, s.DtSaldo, 
			case when p.cdclassinterna like '1.%' or p.cdclassinterna like '2.%' 
					then (-s.vlcredito +(-s.vlDebito))
				 when p.cdclassinterna like '3.%' or p.cdclassinterna like '4.%' or p.cdclassinterna like '5.%'
					then -(-s.vlcredito +(-s.vlDebito))
				end as saldo,
       ult= row_number() over (partition by s.idconta, year(s.dtsaldo), month(s.dtsaldo)
                               order by s.dtsaldo desc)
  from wcont.SD00030 s
  left join wcont.contas p on (s.idconta = p.idconta)
  where p.IdPlanoDeContas = (select e.idplanodecontas from wcont.EmpCont e where e.IdEmpresa = '30')
)
SELECT idconta, DtSaldo, saldo,
       InicioMes= dateadd (month, datediff (month, 0, DtSaldo), 0),
       prim= row_number() over (partition by idconta order by dtsaldo)
  into #SaldoMes
  from tbSaldo_seq
  where ult = 1;

CREATE unique clustered INDEX I1_SaldoMes on #SaldoMes (idconta, InicioMes);

-- coleta primeiro e último mês
declare @Inicio date, @Fim date;
SELECT @Inicio= min (dtsaldo), @Fim= max (dtsaldo)
  from wcont.SD00030;

set @Inicio= dateadd (month, datediff (month, 0, @Inicio), 0);
set @Fim= dateadd (month, datediff (month, 0, @Fim), 0);

--
;with
Periodo as (
SELECT @Inicio as MesAno
union all
SELECT dateadd (month, +1, MesAno)
  from Periodo
  where MesAno < @Fim
),
Periodo_Conta as (
SELECT S.idconta, P.MesAno
  from Periodo as P
       cross join #SaldoMes as S
  where S.prim = 1
        and P.MesAno >= S.InicioMes
),
Esperado as (
SELECT P.idconta, coalesce (S.dtsaldo, eomonth (P.MesAno)) as dtsaldo, saldo,
       P.MesAno,
       prim= row_number() over (partition by P.idconta order by P.MesAno)
  from Periodo_Conta as P
       left join #SaldoMes as S on S.idconta = P.idconta
                                   and S.InicioMes = P.MesAno
),
Completo as (
SELECT idconta, dtsaldo, saldo, MesAno
  from Esperado
  where prim = 1
union all
SELECT E.idconta, E.dtsaldo, coalesce (e.saldo, c.saldo), E.MesAno
  from Completo as C
       inner join Esperado as E on E.idconta = C.idconta
  where E.MesAno = dateadd (month, +1, C.MesAno)
)

SELECT '30', c.idconta, c.dtsaldo, c.saldo, e.idplanodecontas 
  from Completo c, wcont.empcont e
  where e.IdEmpresa = '30'
  order by idconta, dtsaldo;

--
IF Object_ID ('tempDB..#SaldoMes', 'U') is not null
  DROP TABLE #SaldoMes;

Answer 16

Lekão87,

Por padrão toda e qualquer CTE possui um nível padrão de recursividade que esta configurado para o valor inicial de 100 e pode chegar no máximo até 32767.

No seu caso, uma maneira de contornar isso é adicionar a instrução MaxRecursion e passar o número que deseja permitir o uso de recursividade acima do valor padrão 100.

Veja o exemplo abaixo:

-- Exemplo 3 - Criando uma CTE com Union + Recursividade - Simulando uma sequência de números --
;With CTENumerosSequenciais(Numero)
AS
(
     SELECT 1 AS Numero 
     UNION ALL 
     SELECT Numero + 1 AS num FROM CTENumerosSequenciais 
     WHERE Numero < 1000 
)
SELECT * FROM CTENumerosSequenciais -- Vai estourar um erro e agora?
--Go
-- Resolvendo
SELECT * FROM CTENumerosSequenciais
OPTION (MAXRECURSION 0)
Go

Quando definimos o valor de zero, estamos orientando o SQL Server a ignorar o valor padrão de 100 e trabalhar até o máximo que é 32.767.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 17

show, agora foi.

valeuu

Answer 18

show, agora foi.

valeuu

Lekão87,

Ok, que bom.

---

Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

Answer 19

Deleted