Jump to content

[Resolvido] Lista tamanhos de Stock


jafm
 Share

Recommended Posts

Boas...

estou com dificuldades no seguinte, tenho um artigo em varias lojas com vários tamanhos. e queria atraves de um select receber qual a loja com menor artigo por tamanho.

A estrutura da tabela eu recebo o Artigo,loja, qtd/tamanho, ou seja...

Loja A Artigo XS S M L XL XXL

A KA 0 1 1 1 2 3

B KB 1 0 0 0 2 0

C KC 1 2 1 4 5 6

O que pretendia era atraves de um select saber qual a loja com menos quatidade por artigo, neste caso seria loja B no Artigo KB com apenas 2 tamanhos.

Eu queria um count do XS,S,M,L,XL,XLL que fosse inferior a 0

Edited by jafm
Link to comment
Share on other sites

Viva,

Partilho dois métodos diferentes de realizares esta consulta, porém acho que os teus dados de exemplo não devem estar bem, porque presumo que possas ter o mesmo artigo em várias lojas, o que não foi o cenário que apresentaste:

-- Createing a temporary table variable and Inserting scenario data
DECLARE @tabela TABLE (Loja VARCHAR(10), Artigo VARCHAR(10), StockXS INT, StockS INT, StockM INT, StockL INT, StockXL INT, StockXXL INT)
INSERT INTO @tabela VALUES ('A', 'KA', 0, 1, 1, 1, 2, 3), ('B', 'KB', 1, 0, 0, 0, 2, 0), ('C', 'KC', 1, 2, 1, 4, 5, 6)

-- Just to check if data was correctly inserted
-- SELECT * FROM @tabela;

-- Method 1 (using inner queries)
SELECT TOP 1 Loja, Artigo, StockTotal
FROM
(
SELECT Loja, Artigo, StockXS + StockS + StockM + StockL + StockXL + StockXXL AS StockTotal FROM @tabela
) T ORDER BY StockTotal ASC;

-- Method 2 (using a common table expression)
WITH CTE (Loja, Artigo, StockTotal)
AS (SELECT Loja, Artigo, StockXS + StockS + StockM + StockL + StockXL + StockXXL AS StockTotal FROM @tabela)
SELECT TOP 1 * FROM CTE ORDER BY StockTotal ASC;

Pedro Martins

Sharing is Knowledge!

http://www.linkedin.com/in/rechousa

Link to comment
Share on other sites

Boas Rechousa,

Sim, tens razão, o que pretendo é apurar um artigo por várias lojas. Reformulo abaixo a minha consulta.Essa querie ajuda, no entanto eu pretendia era saber qual o tamanho em tenho mais falta de stock, ou seja:

DECLARE @tabela TABLE (Loja VARCHAR(10), Artigo VARCHAR(10), StockXS INT, StockS INT, StockM INT, StockL INT, StockXL INT, StockXXL INT)
INSERT INTO @tabela VALUES ('A', 'KA', 0,0,0,1,2,2), ('B', 'KA', 0,0,1,1,1,1), ('C', 'KA', 0,0,1,1,0,0),('D', 'KA', 0,0,0,1,1,2),('E', 'KA', 0,0,0,0,2,2)
select * from  @tabela

Se repares na consulta acima pela método anterior ia aparecer a loja B com menor stock, só que no fundo eu pretendo é a loja E, tendo esta menos tamanhos disponiveis para venda.

Link to comment
Share on other sites

Sim, de uma forma semelhante:

Repara que quer a loja C, quer a loja E têm 4 tamanhos sem stock.

No primeiro caso retorna a loja C (ordenado por StockTotal ASC). No segundo caso retorna a loja E (ordenado por StockTotal DESC)

DECLARE @tabela TABLE (Loja VARCHAR(10), Artigo VARCHAR(10), StockXS INT, StockS INT, StockM INT, StockL INT, StockXL INT, StockXXL INT)
INSERT INTO @tabela VALUES ('A', 'KA', 0,0,0,1,2,2), ('B', 'KA', 0,0,1,1,1,1), ('C', 'KA', 0,0,1,1,0,0),('D', 'KA', 0,0,0,1,1,2),('E', 'KA', 0,0,0,0,2,2)

-- Just to check if data was correctly inserted
-- SELECT * FROM @tabela;

-- Method 1 (using inner queries)
SELECT TOP 1 Loja, Artigo, TamanhosSemStock, StockTotal
FROM
(
SELECT Loja, Artigo,
   CASE StockXS WHEN 0 THEN 1 ELSE 0 END +
   CASE StockS WHEN 0 THEN 1 ELSE 0 END +
   CASE StockM WHEN 0 THEN 1 ELSE 0 END +
   CASE StockL WHEN 0 THEN 1 ELSE 0 END +
   CASE StockXL WHEN 0 THEN 1 ELSE 0 END +
   CASE StockXXL WHEN 0 THEN 1 ELSE 0 END AS TamanhosSemStock,
   StockXS + StockS + StockM + StockL + StockXL + StockXXL AS StockTotal FROM @tabela
) T ORDER BY TamanhosSemStock DESC, StockTotal ASC;

-- Method 2 (using a common table expression)
WITH CTE (Loja, Artigo, TamanhosSemStock, StockTotal)
AS (SELECT Loja, Artigo,
   CASE StockXS WHEN 0 THEN 1 ELSE 0 END +
   CASE StockS WHEN 0 THEN 1 ELSE 0 END +
   CASE StockM WHEN 0 THEN 1 ELSE 0 END +
   CASE StockL WHEN 0 THEN 1 ELSE 0 END +
   CASE StockXL WHEN 0 THEN 1 ELSE 0 END +
   CASE StockXXL WHEN 0 THEN 1 ELSE 0 END AS TamanhosSemStock,
   StockXS + StockS + StockM + StockL + StockXL + StockXXL AS StockTotal FROM @tabela)
SELECT TOP 1 * FROM CTE
   ORDER BY TamanhosSemStock DESC, StockTotal DESC;

Pedro Martins

Sharing is Knowledge!

http://www.linkedin.com/in/rechousa

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.