Create select to return last time something was non-zero

Asked

Viewed 116 times

1

I have a table called Estoque, where have the columns NOME_PRODUTO, DATA, ,among others. The idea is that when I make one SELECT Using the date as parameter I return all products I had in stock that day, Ex:

SELECT NOME_PRODUTO 
FROM ESTOQUE 
WHERE DATA = '20170220'

This is where things begin to become difficult, I would like that if I did not have a certain product in stock, it was presented the last time that this product was in stock, example:

SELECT NOME_PRODUTO, QUANTIDADE, DATA
FROM ESTOQUE
WHERE DATA = '20170220'

Using as an example, the PRODUTO_D was in stock for the last time on 02/15/2017, the return would be:

    NOME_PRODUTO   QUANTIDADE      DATA 
     PRODUTO_A         10        20170220
     PRODUTO_B         7         20170220
     PRODUTO_C         9         20170220
     PRODUTO_D         3         20170215

Remembering that in stock can contain hundreds of products and every time a product is updated in stock, a new record is created with the name, quantity, date and other information, keeping also the old record. And I won’t always search for the current date.

How do I solve this problem, I tried using the clause CASE, but I didn’t know how to do.

Thank you

sql sql-server windows-azure tsql

  • Do you have any table with product information or just this, stock ?

  • What should happen when you refer to date 20170215? Should the 20170220 day records (PRODUTO_A = 10; PRODUTO_B = 7 and PRODUTO_C = 9) be displayed? Or only records prior to date 20170215 should be considered?

  • @CA_93: How the DATE column is declared?

1 answer

1

"CA_93", there are some approaches to meet what you need. The most traditional is the use of the function row_number() to number the stock lines of each product by date, but in reverse order, where the latest always gets the number 1.

-- código #1
declare @DataEstoque date;
set @DataEstoque= Convert(date, '20/2/2017', 103);

--
with Estoque_Recente as (
SELECT NOME_PRODUTO, QUANTIDADE, DATA, 
       Seq= row_number() over (partition by NOME_PRODUTO order by DATA desc)
  from ESTOQUE
  where DATA <= @DataEstoque
        and QUANTIDADE > 0
)
SELECT NOME_PRODUTO, QUANTIDADE, DATA
  from Estoque_Recente
  where Seq = 1;
  • The @Datastock variable should be declared in the same way as the DATE column.

Browser other questions tagged sql sql-server windows-azure tsql

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