Bring last date of SQL Product

Asked

Viewed 381 times

1

I am performing the following consultation to bring the products with their last sale date:

  SELECT 

  P.ID AS 'Código', 
  p.Nome, 
  CONVERT(decimal(18,2),pe.Valor_Custo) as 'Custo', 
  CONVERT(decimal(18,2),pe.Porcentagem) as 'Mark-up', 
  CONVERT(decimal(18,2),pe.Valor_Venda) as 'Venda',
  e.Quantidade as 'Estoque',

  (SELECT DISTINCT TOP 1 DATA FROM Venda 
  left join Venda_Item_Produto vip on vip.ID_Produto = p.ID
  WHERE VENDA.Data BETWEEN '2018/01/16' AND '2019/01/16' AND vip.ID_Produto = p.ID

  ORDER BY Data DESC) AS 'Data Última Venda'

  FROM Produto P
  left join Produto_Empresa pe on pe.ID_Produto = p.ID
  left join Estoque e on e.ID_Produto = p.ID

However, in the LAST SALE DATE field, it brings me the same date in all the records. What I’m doing wrong?

sql database sql-server
  • 2

    Beyond the DISTINCT be over and the LEFT JOIN can pass to INNER JOIN and remove the vip.ID_Produto = p.ID of the clause WHERE, nothing seems to be wrong. Unless they all actually have the same last sale date.

  • If you want to return a valid last sale date, you have to exchange the left join Venda_Item_Produto for inner join Venda_Item_Produto.

1 answer

0

The error is in the subquery you made, it is only bringing up a table date Venda_Item_Produto, note that if you run only this Select:

SELECT DISTINCT TOP 1 DATA 
  FROM Venda 
  LEFT JOIN Venda_Item_Produto vip 
    ON vip.ID_Produto = p.ID
 WHERE VENDA.Data BETWEEN '2018/01/16' 
   AND '2019/01/16' 
   AND vip.ID_Produto = p.ID

It will bring only one date!

The solution is simple, just use the function MAX on the date, that it will bring the last date of each sale!

Browser other questions tagged sql database sql-server

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