Create select to return last time something was different from zero

Navigation
1

I have a table called Estoque , where it has the columns NOME_PRODUTO , DATA , among others. The idea is that when I make a SELECT Using the date as a parameter, all the products that were in stock on that day are returned to me, Ex: 

SELECT NOME_PRODUTO 
FROM ESTOQUE 
WHERE DATA = '20170220'

That's where things start to get in the way, I wish that if you did not have a certain product in stock, the last time this product was in inventory, 

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

Using as an example, PRODUCT_D was in inventory last time on 2/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 inventory can contain hundreds of products, and every time a product is updated in inventory, a new record is created with the name, quantity, date, and other information, while also keeping the old record. And I'm not always going to search for the current date.

As I solve this problem, I tried using the CASE clause, but I did not know how to do it.

Thank you

    
asked by anonymous 21.02.2017 / 01:55

1 answer

1

"CA_93", there are a few approaches to what you need. The most traditional is to use the row_number () function to number the stock lines of each product by date, but in reverse order, where the most recent one always gets 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 variable @DataEstoque should be declared the same as the DATA column.
21.02.2017 / 12:00
Convert Html to readable text using MVC C # [duplicate] html contains invalid utf-8 character (s) MPDF 6.0