How do I write a store that uses current and previous line values?

Asked

Viewed 1,225 times

2

I have the following table,

    #    id     #   dataTmp     #   referencia  #nProduz#nStock #   id2 #necessi#
    #   115237  #   31-01-2017  #   VL03280103  #   0   #   0   #   6   #   0   #
    #   115238  #   01-02-2017  #   VL03280103  #   0   #   0   #   7   #   0   #
    #   115239  #   01-02-2017  #   VL03280103  #   0   #   0   #   8   #   0   #
    #   115240  #   01-02-2017  #   VL03280103  #   0   #   0   #   9   #   0   #
    #   115241  #   02-02-2017  #   VL03280103  #   0   #   0   #   10  #   0   #
    #   115242  #   02-02-2017  #   VL03280103  #   0   #   0   #   11  #   0   #
    #   115243  #   02-02-2017  #   VL03280103  #   0   #   0   #   12  #   0   #
    #   115244  #   03-02-2017  #   VL03280103  #   0   #   0   #   13  #   0   #
    #   115245  #   03-02-2017  #   VL03280103  #   0   #   0   #   14  #   0   #
    #   115246  #   03-02-2017  #   VL03280103  #   0   #   0   #   15  #   0   #
    #   115247  #   04-02-2017  #   VL03280103  #   0   #   0   #   16  #   0   #
    #   115248  #   04-02-2017  #   VL03280103  #   0   #   0   #   17  #   0   #
    #   115249  #   04-02-2017  #   VL03280103  #   0   #   0   #   18  #   0   #
    #   115250  #   05-02-2017  #   VL03280103  #   0   #   0   #   19  #   0   #
    #   115251  #   05-02-2017  #   VL03280103  #   0   #   0   #   20  #   0   #
    #   115252  #   05-02-2017  #   VL03280103  #   0   #   0   #   21  #   0   #
    #   115253  #   06-02-2017  #   VL03280103  #   0   #   0   #   22  #   0   #
    #   115254  #   06-02-2017  #   VL03280103  #   0   #   0   #   23  #   0   #
    #   115255  #   06-02-2017  #   VL03280103  #   0   #   0   #   24  #   0   #
    #   115256  #   07-02-2017  #   VL03280103  #   0   #   0   #   25  #   0   #
    #   115257  #   07-02-2017  #   VL03280103  #   0   #   0   #   26  #   0   #
    #   115258  #   07-02-2017  #   VL03280103  #   0   #   0   #   27  #   0   #
    #   115259  #   08-02-2017  #   VL03280103  #   0   #   0   #   28  #   0   #
    #   115260  #   08-02-2017  #   VL03280103  #   0   #   0   #   29  #   0   #
    #   115261  #   08-02-2017  #   VL03280103  #   0   #   0   #   30  #   0   #
    #   115262  #   30-01-2017  #   VL03280203  #   0   #   7290    #   1   #   0   #
    #   115263  #   30-01-2017  #   VL03280203  #   0   #   7290    #   2   #   0   #
    #   115264  #   30-01-2017  #   VL03280203  #   0   #   7290    #   3   #   0   #
    #   115265  #   31-01-2017  #   VL03280203  #   0   #   0   #   4   #   0   #
    #   115266  #   31-01-2017  #   VL03280203  #   0   #   0   #   5   #   0   #
    #   115267  #   31-01-2017  #   VL03280203  #   0   #   0   #   6   #   0   #
    #   115268  #   01-02-2017  #   VL03280203  #   0   #   0   #   7   #   0   #
    #   115269  #   01-02-2017  #   VL03280203  #   0   #   0   #   8   #   0   #
    #   115270  #   01-02-2017  #   VL03280203  #   0   #   0   #   9   #   0   #
    #   115271  #   02-02-2017  #   VL03280203  #   0   #   0   #   10  #   0   #
    #   115272  #   02-02-2017  #   VL03280203  #   0   #   0   #   11  #   0   #
    #   115273  #   02-02-2017  #   VL03280203  #   0   #   0   #   12  #   0   #
    #   115274  #   03-02-2017  #   VL03280203  #   0   #   0   #   13  #   0   #
    #   115275  #   03-02-2017  #   VL03280203  #   0   #   0   #   14  #   0   #
    #   115276  #   03-02-2017  #   VL03280203  #   0   #   0   #   15  #   0   #
    #   115277  #   04-02-2017  #   VL03280203  #   0   #   0   #   16  #   0   #
    #   115278  #   04-02-2017  #   VL03280203  #   0   #   0   #   17  #   0   #
    #   115279  #   04-02-2017  #   VL03280203  #   0   #   0   #   18  #   0   #
    #   115280  #   05-02-2017  #   VL03280203  #   0   #   0   #   19  #   0   #
    #   115281  #   05-02-2017  #   VL03280203  #   0   #   0   #   20  #   0   #
    #   115282  #   05-02-2017  #   VL03280203  #   0   #   0   #   21  #   0   #
    #   115283  #   06-02-2017  #   VL03280203  #   0   #   0   #   22  #   0   #
    #   115284  #   06-02-2017  #   VL03280203  #   0   #   0   #   23  #   0   #
    #   115285  #   06-02-2017  #   VL03280203  #   0   #   0   #   24  #   0   #
    #   115286  #   07-02-2017  #   VL03280203  #   0   #   0   #   25  #   0   #
    #   115287  #   07-02-2017  #   VL03280203  #   0   #   0   #   26  #   0   #
    #   115288  #   07-02-2017  #   VL03280203  #   0   #   0   #   27  #   0   #
    #   115289  #   08-02-2017  #   VL03280203  #   0   #   0   #   28  #   0   #
    #   115290  #   08-02-2017  #   VL03280203  #   0   #   0   #   29  #   0   #
    #   115291  #   08-02-2017  #   VL03280203  #   0   #   0   #   30  #   0   #

I need a Stored Procedure Perform the following calculation:

nStock(RowActual)= nStock(RowAnterior) + nProduz(RowActual) - necessidade(RowActual)

I’ve done several codes and I can’t get it to work, someone can help me ?

sql sql-server stored-procedures

  • How will the case of the first line, since there is no previous line?

  • It must always be executed starting with Monday ... never with the first ...

  • In order to calculate the value of the current line and a previous line, you will need to use a cursor, already tried this way?

  • I’ve tried, but I haven’t, yet :)

  • 3

    Which version of SQL Server are you using? In 2012 the function was introduced LAG which returns data from a previous line. In previous versions the workaround usually wrapped two left joins (for *n *and n - 1) using a ranking function such as ROW_NUMBER. Behold some examples.

  • That ID will always be in order and without voids as the example?

  • You want your stored Procedure to calculate all at once or you will pass the current line id and it will only update this?

  • "ID2" is always incremented according to the reference , while "ID" is auto-increment

Show 3 more comments

4 answers

4

To carry out the process you want, one can use a cursor to go through the lines and select the previous value, thus updating the new value:

create procedure atualizar
as
begin
  declare @id          int,
          @nStock      int,
          @nProduz     int,
          @necessidade int;

  set nocount on;

  declare cursorEstoque cursor local fast_forward for
    select tb.id,
           isnull(tb.nProduz, 0),
           isnull(tb.necessidade, 0)
      from tabela tb
     order by tb.id;
  open cursorEstoque
  fetch next from cursorEstoque into @id, @nProduz, @necessidade
  while @@fetch_status = 0
  begin
    -- Pega o valor do último registro
    select top 1 @nStock = isnull(tb.nStock, 0) -- Garante que será calculado caso haja linha anterior
      from tabela tb
     where tb.id < @id
     order by tb.id desc;

    if @nStock is not null -- Será nulo caso não tenha linha anterior
    begin
      set @nStock = @nStock + @nProduz - @necessidade;

      update tb
         set nStock = @nStock
        from tabela tb
       where tb.id = @id;
    end;

    fetch next from cursorEstoque into  @id, @nProduz, @necessidade;
  end;
  close cursorEstoque;
  deallocate cursorEstoque;
end;
go

Another way without needing the select inside the body of cursor:

create procedure atualizar
as
begin
  declare @id             int,
          @nStock         int,
          @nProduz        int,
          @necessidade    int,
          @nStockAnterior int;

  set nocount on;

  declare cursorEstoque cursor local fast_forward for
    select tb.id,
           isnull(tb.nStock, 0),
           isnull(tb.nProduz, 0),
           isnull(tb.necessidade, 0)
      from tabela tb
     order by tb.id;
  open cursorEstoque
  fetch next from cursorEstoque into @id, @nStock, @nProduz, @necessidade
  while @@fetch_status = 0
  begin
    if @nStockAnterior is not null -- Será nulo caso não tenha linha anterior
    begin
      set @nStock = @nStockAnterior + @nProduz - @necessidade;

      update tb
         set nStock = @nStock
        from tabela tb
       where tb.id = @id;
    end;

    set @nStockAnterior = @nStock;

    fetch next from cursorEstoque into  @id, @nStock, @nProduz, @necessidade;
  end;
  close cursorEstoque;
  deallocate cursorEstoque;
end;
go

A third mode is using a variable of type table to store the data with a sequence generated from the sorting in the table taking into account the id:

create procedure atualizar
as
begin
  declare @dados table(sequencia      int,
                       id             int,
                       nStock         int,
                       nProduz        int,
                       necessidade    int);

  set nocount on;

  insert into @dados(sequencia,
                     nStock,
                     nProduz,
                     necessidade)
  select row_number() over(order by tb.id),
         isnull(tb.nStock, 0),
         isnull(tb.nProduz, 0),
         isnull(tb.necessidade, 0)
    from tabela tb
   order by tb.id;

  update tb
     set tb.nStock = tba.nStock + tb.nProduz - tb.necessidade;
    from tabela tb
         inner join @dados tba on tba.sequencia = (tb.sequencia - 1) -- Pega o anterior, afinal o sequencial não pula nenhum número
end;
go

Why use the table and not just something like id - 1? Because with id - 1 if any of the lines are deleted, id will have "holes", which will make logic err at some point.

Observing: To use tables change the name in procedures by the name you are using in your database.

  • 3

    Anyone who has denied the answer may feel free to leave a comment on what is wrong that I can correct. Negative without leaving comment does not make much sense.

  • The lag function will solve his problem without resorting to cursors. Cursors in sql server is only worth resorting to last. IF Sql Server version is 2012 up.

  • @Renatoafonso then post the answer with the function lag. It is not mandatory for a question to have only one answer. And besides, my answer is not wrong, so I don’t see why downvote. Instead of criticizing a response that is not wrong you can offer one that you consider better. By the way, the function lag will not work if you put a parameter limiting the date, for example, since it only works on resultset in question. I recommend reading When to use downvote

  • thank you very much, I haven’t had much time but today I will return and I hope to finish once and for all this problem, I will try the options , Thank you Sorack your reply, was enlightening

  • @Brunoalmeida dispo. Just a detail, which I would indicate for you to use is the third option I made available above precisely to avoid using cursor

3


Bruno, the problem you propose seems to me similar (but not identical) to the issue of bank account statement, where to calculate the balance of the day you must get the balance of the previous day, add the credits of the day and subtract the debits of the day.

The mass of data you posted as an example seems to me that it is not useful to test the solutions proposed, because most of the columns that enter the formula are zeroed. So I chose to create a mass of data of my own for testing, which is at the end of this answer.

PREMISES
In the sample analysis of the table, it is observed that whenever the column value referencia is changed, the column sequential counter id2 is reset. I assume then that the stock value must be recalculated individually for each column value referencia, by sequencing the column id2. That is why, in the solutions proposed, I use the pair (referencia, id2) as a research key.

In the comments you quote that the calculation of nStock should start from the second row; I understand it to be from each value in the column referencia. So I’m assuming the column value nStock, whenever id2 for 1 (first line), it is correct.

SOLUTION 1

The first solution I propose uses CTE recursive. The code is very simple.

-- código #1 v3
with estoqueAtual as (
SELECT id, dataTmp, referencia, id2, nStock, nProduz, necessidade,
       novoStock= nStock
  from SeguinteTabela
  where id2 = 1
union all
SELECT T2.id, T2.dataTmp, T2.referencia, T2.id2, 
       T2.nStock, T2.nProduz, T2.necessidade,
       (T1.novoStock + T2.nProduz - T2.necessidade)
  from estoqueAtual as T1
       inner join Seguintetabela as T2 
                  on T1.referencia = T2.referencia and T1.id2 = (T2.id2 -1)
)
SELECT id, Convert(char(10), dataTmp, 103) as dataTmp, referencia, id2, 
       nStock, nProduz, necessidade, novoStock
  from estoqueAtual
  --where dataTmp >= 
  order by referencia, id2;

For you to test with the database table, replace the text SeguinteTabela by the actual table name.

This is the result obtained by using the mass of data created.

inserir a descrição da imagem aqui

To update the table, code #1 is slightly modified, replacing the final SELECT with UPDATE:

-- código #3 v2
with estoqueAtual as (
SELECT id, dataTmp, referencia, id2, nStock, nProduz, necessidade,
       novoStock= nStock
  from SeguinteTabela
  where id2 = 1
union all
SELECT T2.id, T2.dataTmp, T2.referencia, T2.id2, T2.nStock, T2.nProduz,
       T2.necessidade, (T1.novoStock + T2.nProduz - T2.necessidade)
  from estoqueAtual as T1
       inner join Seguintetabela as T2 
                  on T1.referencia = T2.referencia and T1.id2 = (T2.id2 -1)
)
UPDATE T4
  set nStock= T3.novoStock
  --output deleted.id, deleted.nStock, inserted.nStock
  from estoqueAtual as T3
       inner join SeguinteTabela as T4 on T3.id = T4.id
  where T4.nStock <> T3.novoStock
        --and T3.dataTmp >=

For you to test with the database table, replace the text SeguinteTabela by the actual table name.

SOLUTION 2

The second solution uses another approach. The result is the same as code #1.

-- código #4 v2
SELECT T1.id, T1.dataTmp, T1.referencia, T1.id2, 
       T1.nStock, T1.nProduz, T1.necessidade,
       novoStock= case when T1.id2 = 1
                       then T1.nStock
                       else ((SELECT nStock 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 = 1) +
                             (SELECT Sum(nProduz - necessidade) 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 > 1
                                      and T2.id2 <= T1.id2)
                            )
                  end
  from SeguinteTabela as T1
  --where T1.dataTmp >=
  order by T1.referencia, T1.id2;

To update, code #4 is transformed as CTE, and then:

-- código #5 v2
with estoqueAtual as (
SELECT T1.id, 
       novoStock= case when T1.id2 = 1
                       then T1.nStock
                       else ((SELECT nStock 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 = 1) +
                             (SELECT Sum(nProduz - necessidade) 
                                from SeguinteTabela as T2
                                where T2.referencia = T1.referencia
                                      and T2.id2 > 1
                                      and T2.id2 <= T1.id2)
                            )
                  end
  from SeguinteTabela as T1
  --where dataTmp >=
)
UPDATE T4
  set nStock= T3.novoStock
  --output deleted.id, deleted.nStock, inserted.nStock
  from estoqueAtual as T3
       inner join SeguinteTabela as T4 on T3.id = T4.id
  where T4.nStock <> T3.novoStock;

Here is the code that generates the mass of data for testing.

-- código #2
CREATE TABLE SeguinteTabela (
  id int, dataTmp date, referencia varchar(20), 
  nProduz int, nStock int, id2 int, necessidade int);

set dateformat dmy;
truncate table SeguinteTabela;
INSERT into SeguinteTabela values 
  (115262, '30-01-2017', 'VL03280203', 20, 100, 1, 5),
  (115263, '30-01-2017', 'VL03280203', 30, 0, 2, 10),
  (115264, '30-01-2017', 'VL03280203', 25, 0, 3, 100),
  (115265, '31-01-2017', 'VL03280203', 35, 0, 4, 0),
  (115266, '31-01-2017', 'VL03280203', 40, 0, 5, 120),
  (115267, '28-01-2017', 'VL03280303', 20, 0, 1, 0),
  (115268, '29-01-2017', 'VL03280303', 30, 0, 2, 10),
  (115269, '30-01-2017', 'VL03280303', 20, 0, 3, 18);
go

  • To complement: the maximum recursion standard in SQL Server is 100 interactions. If the table has more than this it will be necessary to set the option maxrecursion to the desired size (0 is infinite) option (maxrecursion 0)

  • thanks now I just have to put the date >= today , just to update from the current day forward being that back is updating based on another condition is perfect the query ... was the strand I would create after studying the first option above , thank you very much

  • @Brunoalmeida: I added solution 2, which uses a non-recursive approach. Based on the same premises.

  • @Brunoalmeida: If you limit by date, then I suggest using the solution 2. Note that the WHERE clause has been added.

  • Hello, I think the solution 1 works perfectly I haven’t had a chance to try it because of the lack of time but I will do it as soon as possible, I think by adding the following Where T4.nStock <> T3.novoStock AND dataTmp>=(select dateadd(dd,@i, cast(getdate() as date))) ; should work !

0

Using cursors in this case is a procedural way of thinking about working with SQL which can make your process slow depending on how this cursor is written. The best is to remember set theory and think of its single table as two tables where the intersection or JOIN is done with id = id-1.

MS SQL SERVER has a very interesting feature which is the UPDATE FROM if you want to update the data table.

I would make a Storedprocedure with the following queries. Use them as you need. I am giving the name of the source table tabela_dados.

Consultation:

-- Para simples consulta

SELECT nStock = RowAnterior.nStock + RowActual.nProduz - RowActual.necessidade
FROM tabela_dados AS RowActual
INNER JOIN tabela_dados AS RowAnterior ON (RowActual.id = RowAnterior.id-1)

Update:

-- Para atualizar a tabela

UPDATE RowActual SET RowActual.nStock = RowAnterior.nStock + RowActual.nProduz - RowActual.necessidade
FROM tabela_dados AS RowActual
INNER JOIN tabela_dados AS RowAnterior ON (RowActual.id = RowAnterior.id-1)

  • If for some reason a half line is deleted your answer will fail, after all it only considers if the id is the number immediately below

  • Exactly. As this was not evidenced in the sample of the question there is no way to know if it will occur. We do not know whether the sampling is a physical or temporary table, or whether it allows deletion of records. Anyway this can be circumvented with a temporary table or variable 'TABLE' with a field 'seq_id IDENTITY' as you presented in your reply, however, I would do it without cursor. It is up to the analyst to identify the need.

0

with estoqueAtual as (
SELECT id_Matrix, dataTmp, referencia, id2, nStock, nProduz,         necessidade,turno,
       novoStock= nStock
  from Matrix
  where dataTmp=(select dateadd(dd,0, cast(getdate() as date))) and turno=1
union all
SELECT T2.id_Matrix, T2.dataTmp, T2.referencia, T2.id2, T2.nStock, T2.nProduz,
       T2.necessidade,t2.turno, (T1.novoStock + T2.nProduz - T2.necessidade)
  from estoqueAtual as T1
       inner join Matrix as T2 
                  on T1.referencia = T2.referencia and T1.id2 = (T2.id2 -1)
)
UPDATE T4
  set nStock= T3.novoStock
  --output deleted.id, deleted.nStock, inserted.nStock
  from estoqueAtual as T3
       inner join Matrix as T4 on T3.id_Matrix = T4.id_Matrix
  where T4.nStock <> T3.novoStock and T3.dataTmp >=(select dateadd(dd,0,     cast(getdate() as date)))
  option (maxrecursion 0)
  ;

Here Is the Final code was just add the date and pass the need to turn=2 which had not yet put in the comic ... At the moment it is working perfectly , Thank you very much !

Browser other questions tagged sql sql-server stored-procedures

You are not signed in. Login or sign up in order to post.