CODEWITHSUNDEEP
sql-server
Jackal
Jackal

Reputation: 3521

How to sum random rows column in where clause SQL

I have a stored procedure that selects a number of random rows from a table based on a parameter and I need to run this in a loop so it constantly selects random rows until the sum of them is accepted

So far I haven't started the loop yet because I can't figure out how do i use sum of a column as a condition

GO
IF OBJECT_ID('dbo.spx_SELECT_RandomLocalizacoes') IS NOT NULL
DROP PROCEDURE spx_SELECT_RandomLocalizacoes
GO
CREATE PROCEDURE spx_SELECT_RandomLocalizacoes
        @Localizacoes_Max int,
        @Filtro int,
        @Armazem int
AS
BEGIN
SET NOCOUNT ON  

        SELECT TOP (@Localizacoes_Max) * FROM xInventario 
        WHERE Armazem = @Armazem AND SUM(Quantidade) > @Filtro
        ORDER BY newid()

END

The final result should be a procedure that returns me the rows that obey the condition

EDIT:

I forgot to add that, I have to return the select statement with the random rows with the same seed so i can only do that query once

Upvotes: 0

Views: 57

Answers (1)

Cato
Cato

Reputation: 3701

you can treat your query as a sub-query and SUM it, then apply whatever logic you are looking for

if (SELECT SUM(Randoms.YourField)   FROM (SELECT TOP (@Localizacoes_Max) * FROM xInventario 
        WHERE Armazem = @Armazem AND SUM(Quantidade) > @Filtro 
        ORDER BY newid()) AS Randoms) = @Target
BEGIN

  --do stuff

END

Upvotes: 1

Related Questions