Select com valor acumulativo

Question

Bom dia, estou com um problema que não estou encontrando um forma de resolver.

Preciso pegar resultado até um determinado valor.
Exemplo segue dados de testes abaixo.

create table temp(
	id int identity(1,1),
	produto int, 
	nomeProduto varchar(128),
	qtdProduto decimal(18,3)
)
go

insert into temp
	(produto, nomeProduto, qtdProduto)
values
	(1, 'PRODUTO 1', 10), 
	(1, 'PRODUTO 1', 18), 
	(1, 'PRODUTO 1', 20), 
	(2, 'PRODUTO 2', 10), 
	(2, 'PRODUTO 2', 10), 
	(3, 'PRODUTO 1', 50)
go

Exemplo, meu problema é o seguinte, preciso pegar os produtos 1 até chegar o valor de 25, portando a id 3 não deveria ser apresentada. A dois passaria mais aí nesse caso tudo bem.
Os produtos 2 quero pegar 30, sendo que so existe 10 quero pegar todos
O produto 3 eu so queria pegar 30, porem como tambem so existe um registro ele deve ser apresentado.

Ou seja eu preciso ir pegando linhas até chegar a um determinado valor. Se um registro deve ser pego para se chegar ao valor base e passar nao tem problema, o que nao posso é pegar mais um registro após a soma já ter passado o valor base.

Alguma idéia de como eu poderia resolver o problema?
Obrigado!

Answer 1

Bom dia,

Se você estiver utilizando o SQL Server 2012 ou mais recente experimente utilizar a função Sum com a clausula Over para obter a soma da quantidade até a linha. Ex:

with CTE_Sum as
(
    select
        produto,
        nomeProduto,
        qtdProduto,
        sum(qtdProduto) over(partition by produto order by id) as Soma
    fron temp
)

select * from CTE_Sum
-- where Soma <= 25

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

rafa

acho que tem que montar store procedure

create procedure pega_produto @nomeProduto varchar(128),@qtdProduto decimal(18,3) as
with ordenatorio as (select id,produto,nomeProduto,qtdProduto,row_number()over(order by id) n
from temp where nomeProduto=@nomeProduto and @qtdProduto>0),
somatorio as (select top 1 id,produto,nomeProduto,qtdProduto,n,qtdProduto soma
from ordenatorio order by n union all
select u.id,t.produto,t.nomeProduto,u.qtdProduto,u.n,cast(t.soma+u.qtdProduto as decimal(18,3))
from somatorio t inner join ordenatorio u on u.n=t.n+1
where t.soma<@qtdProduto)
select id,produto,nomeProduto,qtdProduto,soma from somatorio order by id
go
---
exec pega_produto 'produto 1',25
exec pega_produto 'produto 2',30
exec pega_produto 'produto 3',30

Answer 3

rafa

acho que tem que montar store procedure

create procedure pega_produto @nomeProduto varchar(128),@qtdProduto decimal(18,3) as
with ordenatorio as (select id,produto,nomeProduto,qtdProduto,row_number()over(order by id) n
from temp where nomeProduto=@nomeProduto and @qtdProduto>0),
somatorio as (select top 1 id,produto,nomeProduto,qtdProduto,n,qtdProduto soma
from ordenatorio order by n union all
select u.id,t.produto,t.nomeProduto,u.qtdProduto,u.n,cast(t.soma+u.qtdProduto as decimal(18,3))
from somatorio t inner join ordenatorio u on u.n=t.n+1
where t.soma<@qtdProduto)
select id,produto,nomeProduto,qtdProduto,soma from somatorio order by id
go
---
exec pega_produto 'produto 1',25
exec pega_produto 'produto 2',30
exec pega_produto 'produto 3',30

Respondedor,

Pode ser uma solução viável sim o uso de Stored Procedure!

---

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