Update item table with stock in any branch

Question

All right Guys,

I have a table with stock of products from a network of 13 stores. I want to block items that have no stock in any store and that have not had sales in the last year or that have only been registered, but have not sold. I tried the script below:

UPDATE PRXLJ
SET  Flg_BlqCom = 1, Flg_BlqVen = 1
WHERE Qtd_Saldo= 0
AND (YEAR(Dat_UltVen) <2017
OR Dat_UltVen IS NULL)

where:

• Flg_blqcom = 1 blocked for purchase
• Flg_blqven = 1 blocked for sale
• Flg_blqcom = 0 unlocked for purchase
• Flg_blqven = 0 unlocked for sale

The problem is that even in the central server the stocks are distributed in the table store by store and do not want to block if you have at least one unit in any branch. the same product code repeats 13 times, one for each store. When I ran this script he blocked the products according to the stock and last sale of each store, blocking a product in some stores and others not.

The structure of the table looks like this:

I need a script that blocks only the product that has no stock in any store and that has not been sold after 01/01/2017 in any store as well.

Using Sql Server 2008 This table has around 60 thousand items (considering that there are 13 stores, are around 780mil lines) and I want to leave unlocked some 15 thousand items or less that are actually active. For tax reasons I can’t delete old items, just block them. I don’t know if the information was enough for understanding, but if you need more information let me know.

Answer 1

Make sure the following suggestion meets your needs.

-- código #1 v2
with Bloquear as (
SELECT Cod_produt
  from PRxLJ
  group by Cod_produt
  having max (Qtd_Saldo) = 0
         and max (coalesce(year(Dat_UltVen), 0)) < 2017
)
UPDATE PRxLJ
  set Flg_BlqCom = 1, Flg_BlqVen = 1
  where Cod_Produt in (SELECT Cod_produt from Bloquear);

The above code assumes that there will always be information in the Qtd_balance column and that it will never be negative.

---

Regarding your second question, unlock what shouldn’t be locked, here’s an option:

-- código #2
with Liberar as (
SELECT Cod_produt
  from PRxLJ
  where Flg_BlqCom = 1
        and Flg_BlqVen = 1
  group by Cod_produt
  having max (Qtd_Saldo) > 0
         or max (coalesce(year(Dat_UltVen), 0)) >= 2017
)
UPDATE PRxLJ
  set Flg_BlqCom = 0, Flg_BlqVen = 0
  where Cod_Produt in (SELECT Cod_produt from Liberar);

So that the transaction log file doesn’t grow much, the CTE Liberar only returns products that are blocked in at least one store and that meet the requirements. That is why the WHERE clause has been added.

Answer 2

Man, perfect solution. I tried to do something like that, but I couldn’t. If I want to do the reverse too, unlock what has balance or sale in any store, the code below is right?

    with Liberado as (
SELECT Cod_produt
  from PRxLJ
  group by Cod_produt
  having sum (Qtd_Saldo) <> 0
         or max (coalesce(year(Dat_UltVen), 0)) > 2017
)
UPDATE PRxLJ
  set Flg_BlqCom = 0, Flg_BlqVen = 0
  where Cod_Produt in (SELECT Cod_produt from Liberado);