Make item table update with stock on any branch

Question

Make item table update with stock on any branch

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

Alright, people,

I have a table with stock of products in a network with 13 stores. I want to block items that have no inventory in any store and that have not had sales in the last year or that were only registered, but did not sell. 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 on the central server the stores are distributed in the store-to-store table and do not want to block if you have at least one drive in any branch. the same product code is repeated 13 times, one for each store. When I ran this script, it blocked the products according to the stock and last sale of each store, blocking one product in some stores and not in other stores.

The table structure 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 about 60 thousand items (considering that there are 13 stores, they are around 780 thousand lines) and I want to leave unlocked about 15 thousand items or less that are in fact active. For tax purposes I can not delete the old items, just block them. I do not know if the information was enough for understanding, but if you need more data let me know.

sql-server sql-update

asked by anonymous 26.01.2018 / 18:48

2 answers

0

Dude, perfect solution. I tried to do something like that, but I could not. If I want to do the reverse also, unlock what has balance or sale in some 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);

27.01.2018 / 12:53

How far should snippets be used? [closed] How do I make a friendly url with PHP? [duplicate]

score 0 · Accepted Answer

Make sure the following suggestion meets what you need.

-- 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 code above assumes that there will always be information in the Qtd_Saldo column and that it will never be negative.

Regarding your second question, unlock what should not be blocked , 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);

In order for the transaction log file to not grow too large, CTE Liberar only returns products that are locked in at least one store and that meet the requirements. That is why the WHERE clause was added.