locked
select record based on condition in sql RRS feed

  • Question

  • Hello all, 

    i have the following data in table 

    ID    event 

    1 xmas
    1 promo
    2 xmas
    2 promo
    2 promo
    3 xmas


     requirement is 

    1) if ID with event has one xmas and one promo  we have to ignore that records

    2) if ID with event has one xmas and multiple promo i have to ignore multiple promo records and show only ID with xmas record

    3) if ID with event has only xmas without promos i have to show that record.

    Result should be like this from above table 

    when i select the data i need only

    ID  EVENT

    2    XMAS

    3   XMAS

    Please help me how can i solve thiss

    Thanks in advance

    Monday, October 13, 2014 3:26 AM

Answers

  • Try below

    create table #temp1
    (
    ID int, 
    [event] varchar(30)
    )
    GO
    insert into #temp1
    select 1,  'xmas' union all  
    select 1,  'promo' union all  
    select 2,  'xmas' union all  
    select 2,  'promo' union all  
    select 2,  'promo' union all  
    select 3,  'xmas' 
    
    select ID, 'xmas' [EVENT] from #temp1 
    group by ID
    having sum(case when event = 'promo' then 1 else 0 end) <> 1

    Thanks

    Saravana Kumar C

    Monday, October 13, 2014 4:56 AM
    Answerer
  • This would be enough I guess

    SELECT ID,Event
    FROM
    (
    SELECT ID,Event,
    SUM(CASE WHEN Event='promo' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS promocnt
    FROM table
    )t
    WHERE promocnt <> 1
    AND  Event = 'xmas'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, October 13, 2014 5:48 AM
    Answerer

All replies

  • Hi,

    Here is one option:

    SELECT
    	AllRows.Id ,
    	AllRows.Event
    FROM
    	dbo.Test AS AllRows
    LEFT OUTER JOIN
    	(
    		SELECT
    			Id
    		FROM
    			dbo.Test
    		WHERE
    			Event = N'promo'
    		GROUP BY
    			Id
    		HAVING
    			COUNT (*) = 1
    	)
    	AS
    		RelevantRows
    ON
    	AllRows.Id = RelevantRows.Id
    WHERE
    	AllRows.Event = N'xmas'
    AND
    	RelevantRows.Id IS NULL;
    GO
    

    I hope this helps...

    --------------------------------------------
    Guy Glantser
    SQL Server Consultant & Instructor
    Madeira - SQL Server Services
    http://www.madeirasql.com

    Monday, October 13, 2014 4:15 AM
  • Try below

    create table #temp1
    (
    ID int, 
    [event] varchar(30)
    )
    GO
    insert into #temp1
    select 1,  'xmas' union all  
    select 1,  'promo' union all  
    select 2,  'xmas' union all  
    select 2,  'promo' union all  
    select 2,  'promo' union all  
    select 3,  'xmas' 
    
    select ID, 'xmas' [EVENT] from #temp1 
    group by ID
    having sum(case when event = 'promo' then 1 else 0 end) <> 1

    Thanks

    Saravana Kumar C

    Monday, October 13, 2014 4:56 AM
    Answerer
  • This would be enough I guess

    SELECT ID,Event
    FROM
    (
    SELECT ID,Event,
    SUM(CASE WHEN Event='promo' THEN 1 ELSE 0 END) OVER (PARTITION BY ID) AS promocnt
    FROM table
    )t
    WHERE promocnt <> 1
    AND  Event = 'xmas'


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Monday, October 13, 2014 5:48 AM
    Answerer
  • Be careful what you assume and what you imply with your description and sample data.  Can multiple "XMAS" exist for the same ID?  Can any other value exist for EVENT? 

    Monday, October 13, 2014 12:47 PM
    Answerer