If you only need the customer’s name, you may not return the other field. When you bring the data
also in consultation, the same customer with different dates is returned.
SELECT DISTINCT p.ID, p.Nome_Fantasia
FROM Venda v
INNER JOIN Cliente cli ON cli.ID_Pessoa = v.ID_Pessoa
INNER JOIN Pessoa p ON p.ID = cli.ID_Pessoa
WHERE Data < '2017-04-24 00:00:00.000'
ORDER BY p.ID
If you need, for example, the date of the user’s last purchase, you can use it as follows:
SELECT DISTINCT p.ID, p.Nome_Fantasia, MAX(v.Data)
FROM Venda v
INNER JOIN Cliente cli ON cli.ID_Pessoa = v.ID_Pessoa
INNER JOIN Pessoa p ON p.ID = cli.ID_Pessoa
WHERE Data < dateadd(year, -1, convert(varchar(10), getdate(), 120))
GROUP BY p.ID, p.Nome_Fantasia
ORDER BY p.ID
Thus, it will bring the user data "inactive" at least one year with the date of the last purchase.
Detail: to ensure that your query will always bring "a year ago", I changed to the date use DATEADD
.
You are grouping because one of the columns has different value. You should use Group by
– arllondias
If you only want customers, why are you using Data on
select
withdistinct
? Will bring all records of different dates, try to remove the date of theselect
– Ricardo Pontual