I'm trying to make a SELECT that brings me only customers who have not bought for 1 year back without duplicating the client.
Below the code I'm trying to do:
SELECT DISTINCT p.ID, p.Nome_Fantasia, 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 < '2017-04-24 00:00:00.000'
order by p.ID
However, even using DISTINCT, it brings me Duplicate Clients.
As already noted by the staff, if you include the date of sale in SELECT the customer will appear once for each sale he had before the deadline set (1 year ago). In this case it is necessary to remove the pure date from the select, but it makes sense to include a GROUP BY and a MAX on the date so that it returns the date of the most recent sale made to this client.
Taking advantage of it, I fixed a concept error in the query. You should not base your sales query if you want to consider customers who have never bought (theoretically these guys also meet your restriction). In this case, the correct one would be to base the query on Cliente and use OUTER JOIN to check sales.
To make the query dynamic, I used the DATEADD function to represent a year ago from the current date (365 passed).
Your query would look like this:
SELECT DISTINCT p.ID, p.Nome_Fantasia, Max(v.Data)
FROM Cliente cli
JOIN Pessoa p on p.ID = cli.ID_Pessoa
LEFT JOIN Venda v on cli.ID_Pessoa = v.ID_Pessoa
WHERE v.Data IS NULL OR v.Data < DATEADD(day, -365, GETDATE())
GROUP BY p.ID, p.Nome_Fantasia
ORDER BY p.ID
Since we started using OUTER JOIN , we need to contemplate the possibility that there was no sale also in the WHERE
Hope it helps
If you need only the client name, it may not return the other fields. When you bring data into the query, the same client 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 your 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
This will bring the data of the user "inactive" for at least a year with the date of the last purchase.
Detail: To ensure that your query will always bring "a year back", I have changed the date to use DATEADD .
But what's wrong with bringing duplicate clients? Can not the same customer buy more than once on the same day, for example?
In any case, try to use BETWEEN (more performative) and GROUP BY (to group by customer ID)
SELECT DISTINCT p.ID, p.Nome_Fantasia, 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 BETWEEN '2017-04-24 23:59:59' AND '2018-04-24 10:15:55')
GROUP BY cli.ID