For/Foreach Sql Server For Each Separate Column Data

Question

I need to execute a command SQL to update a table, the intention of this update would be to update a column with sequential number per company, e.g.: I have a table Sale in this table have the column Empresaid for each company I intend to create a sequence of sale type Business1 sale 1,2,3... Company 2 sale 1,2,3 and so the ID does not respect this order, well the problem is in the following, I created the column and need to give an update to do this for the records that already exist, I got with the following code:

DECLARE @NumeroSequencia int
SET @NumeroSequencia = 0
UPDATE Venda
SET @NumeroSequencia = NumeroSequencia = @NumeroSequencia + 1 where 
EmpresaID = 1

But where Empresaid = 1 I wanted to do something like a foreach in the example but in Sql:

--MONTA UMA LISTA COM OS IDS DE CADA EMPRESA CADASTRADA
declare listaEmpresas = select id from empresa

--PARA CADA EMPRESA EXECUTA O UPDATE COM A SEQUENCIA DAS VENDAS
foreach (var item in listaEmpresas){
DECLARE @NumeroSequencia int
SET @NumeroSequencia = 0
UPDATE Venda
SET @NumeroSequencia = NumeroSequencia = @NumeroSequencia + 1 where 
EmpresaID = item --AQUI SERIA O ID DE CADA EMPRESA SEGUINDO O FOREACH
}

Answer 1

I propose a solution that does not use ties (at least it does not explicitly).

We start with setting an example table and loading some records.

CREATE TABLE #Vendas 
(
   VendaID        INT IDENTITY(1, 1),
   EmpresaID      INT,
   ProdutoID      INT,
   Quantidade     INT,
   DataVenda      DATE,
   --Nova coluna com ID de venda por empresa
   EmpresaVendaID INT,
);

--Carregar alguns registos na tabela 
INSERT INTO #Vendas(EmpresaID, ProdutoID, Quantidade, DataVenda)VALUES
(1, 1, 2, '2017-01-01'),
(1, 2, 2, '2017-02-01'),
(1, 2, 3, '2017-03-01'),
(1, 1, 5, '2017-04-01'),
(2, 1, 5, '2017-01-01'),
(2, 1, 4, '2017-02-01');

--SELECT * FROM #Vendas;

Before UPDATE the Empresavendaid column shows NULL for all records:

VendaID EmpresaID   ProdutoID   Quantidade  DataVenda    EmpresaVendaID
1       1           1           2           01.01.2017   NULL
2       1           2           2           01.02.2017   NULL
3       1           2           3           01.03.2017   NULL
4       1           1           5           01.04.2017   NULL
5       2           1           5           01.01.2017   NULL
6       2           1           4           01.02.2017   NULL

Now let’s update the table. The idea is to generate a sequence for each Empresaid, sequence that always starts in 1. You may have to slightly change the invocation of the function ROW_NUMBER depending on the structure of your table. In this case I assume the existence of a primary key column in the #Sales table (Vendaid).

UPDATE Vd
   SET EmpresaVendaID = Seq.Sequencia
  FROM #Vendas Vd
 INNER JOIN
  (
      SELECT VendaID, 
             ROW_NUMBER() OVER (PARTITION BY EmpresaID ORDER BY VendaID) Sequencia
        FROM #Vendas
  ) Seq
    ON Seq.VendaID = Vd.VendaID
;

More concise alternative

UPDATE Vd
   SET EmpresaVendaID = Sequencia
  FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY EmpresaID ORDER BY VendaID) Sequencia FROM #Vendas ) Vd
;

--SELECT * FROM #Vendas;

After UPDATE this will be the result:

VendaID EmpresaID   ProdutoID   Quantidade  DataVenda    EmpresaVendaID
1       1           1           2           01.01.2017   1
2       1           2           2           01.02.2017   2
3       1           2           3           01.03.2017   3
4       1           1           5           01.04.2017   4
5       2           1           5           01.01.2017   1
6       2           1           4           01.02.2017   2

Stays here the link to the code

Answer 2

A possible solution would be to use the function ROW_NUMBER() with Partition within a Common_table_expression (CTE) to generate sequential sales by company and then update the Sales table. The query would look like this:

with Venda_Cte (VendaID, NumeroSequencia)
as
(
  --query cte usando a função ROW_NUMBER com partition para gerar
  --o sequencial da venda para cada empresa
  select VendaID
         ,row_number() over (partition by EmpresaID order by VendaID)
    from Venda
)
--atualizando a tabela Venda com o sequencial da cte
update Venda
   set NumeroSequencia = Venda_Cte.NumeroSequencia  
  from Venda 
  join Venda_Cte on Venda.VendaID = Venda_Cte.VendaID;