July 7, 2018 at 9:08 am
Hi ,
I have some issue and i have no idea how to solve it .
I need to write a query that will give me the people who only bought on 2015 .
Some help please ?
id yearbought
1 2015
1 2016
1 2017
2 2015
3 2015
3 2017
July 7, 2018 at 9:29 am
What have you tried so far? Do you only need to return the number of the customer, or other data as well? If the latter, you might want to have a look into EXISTS (specifically, you'll want a NOT EXISTS).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 7, 2018 at 9:37 am
i thought about this :
select id
into #temp1
from purchases
where year=2015
select id
into #temp2
from purchases
where year<>2015
select id
from #temp1
where not exists (select id from #temp2)
July 7, 2018 at 9:54 am
No need for the temporary tables, you can have multiple clauses in your WHERE clause. Simply:FROM YourTable YT
WHERE YT.[year] = 2015
AND NOT EXISTS (SELECT 1 FROM YourTable sq WHERE [year] <> 2015 AND sq.id = YT.id);
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 7, 2018 at 10:16 am
tnx!!!!!!!
July 8, 2018 at 4:18 am
For fun, here is a single table scan query that does the same
😎
USE TEEST;
GO
SET NOCOUNT ON;
-- https://www.sqlservercentral.com/Forums/1975311/Query-Help
;WITH SAMPLE_DATA(ID,YEARBOUGHT) AS
(
SELECT 1,2015 UNION ALL
SELECT 1,2016 UNION ALL
SELECT 1,2017 UNION ALL
SELECT 2,2015 UNION ALL
SELECT 3,2015 UNION ALL
SELECT 3,2017
)
SELECT
SD.ID
,MAX(SD.YEARBOUGHT) AS YEARBOUGHT
FROM SAMPLE_DATA SD
GROUP BY SD.ID
HAVING COUNT(DISTINCT SD.YEARBOUGHT) = 1
AND MAX(SD.YEARBOUGHT) = 2015;
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply