Query Help

  • RazLinky

    SSC Enthusiast

    Points: 106

    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

  • Thom A

    SSC Guru

    Points: 98273

    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.

  • RazLinky

    SSC Enthusiast

    Points: 106

    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)

  • Thom A

    SSC Guru

    Points: 98273

    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.

  • RazLinky

    SSC Enthusiast

    Points: 106

    tnx!!!!!!!

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply