2
I’m trying to realize a SELECT that brings me only customers who haven’t bought for 1 year without duplicating the customer.
Down with the code I’m trying to:
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
But even using DISTINCT, it brings me Duplicate Customers.
3
As noted by the staff, if you include the date of sale on SELECT the customer will appear once for each sale he has had before the set deadline (1 year ago). In that case, it is necessary to remove the pure select date, but it makes sense to include a GROUP BY and a MAX on the date for it to return to you the date of the most recent sale made to this customer.
Taking advantage, I corrected a misconception in the consultation. You shouldn’t base your query on the sale if you want to consider customers who never bought (theoretically these guys also meet your restriction). In this case, the correct one would be to base the consultation on Cliente and use a OUTER JOIN to check sales.
To make the query dynamic, I used the function DATEADD to represent a year ago from the current date (365 past).
Your appointment 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
How do we use one OUTER JOIN, provision should be made for the possibility that the clause WHERE
I hope it helps
2
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.
0
But what’s wrong with bringing in duplicate customers? The same customer can’t buy more than once in a day, for example?
In any case, try using BETWEEN (more performative) and GROUP BY (to group by client 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
Browser other questions tagged sql sql-server
You are not signed in. Login or sign up in order to post.
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
selectwithdistinct? Will bring all records of different dates, try to remove the date of theselect– Ricardo Pontual