locked
need a query to selectively remove a special condition. RRS feed

  • Question

  • I need help on a query with customer data like this:

    custID    eventName
    1    No Show
    2    Halloween
    2    No Show
    3    New Year Eve
    3    No Show
    3    xmas
    4    July4th

    I need a way to filter out the "No Show"   only if    there are other Events for that customer.
    For example CustID1 will keep the "No Show" as the result.
    CustID3 remove the "No Show" and show the remaining one "New Year Eve" and "xmas"



    custID    eventName
    1    No Show
    2    Halloween
    3    New Year Eve
    3    xmas
    4    July4th


    declare    @customerEvent    table
    (
              custID    int
            , eventName    varchar(100)
    )

    insert into @customerEvent
    select
    1, 'No Show'
    union
    select
    2, 'No Show'
    union
    select
    2, 'Halloween'
    union
    select
    3, 'No Show'
    union
    select
    3, 'New Year Eve'
    union
    select
    3, 'xmas'
    union
    select
    4, 'July4th'





    select * from @customerEvent


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, October 25, 2012 4:03 AM

Answers

  • declare    @customerEvent    table
    (
              custID    int
            , eventName    varchar(100)
    )
    
    insert into @customerEvent
    select
    1, 'No Show'
    union
    select
    2, 'No Show'
    union
    select
    2, 'Halloween'
    union
    select
    3, 'No Show'
    union
    select
    3, 'New Year Eve'
    union
    select
    3, 'xmas'
    union
    select
    4, 'July4th'
    
    
    SELECT * FROM @customerEvent
    EXCEPT
    (
    SELECT custID
    	   ,a.eventName
    FROM
    	(
    	SELECT CustId,eventName,ROW_NUMBER() OVER(PARTITION BY custID ORDER BY custID) as rn 
    	FROM @customerEvent
    	) a
    WHERE a.rn <> 1 AND a.eventName LIKE 'No Show'
    )

    Output :

    custID	eventName
    1	No Show
    2	Halloween
    3	New Year Eve
    3	xmas
    4	July4th


    Narsimha

    • Marked as answer by light_wt Thursday, October 25, 2012 12:41 PM
    Thursday, October 25, 2012 4:33 AM
  •  ; with mycte as
     (
    SELECT custID, eventname,count(eventname) over(partition by custid) cnt from @customerEvent )
    select custID, eventname from mycte where  eventname<>'No Show' Or (eventname='No Show' and cnt=1)

    • Marked as answer by light_wt Thursday, October 25, 2012 12:44 PM
    Thursday, October 25, 2012 5:02 AM

All replies

  • Hi Light,

    Try something like this..

    declare

    @customerEvent table

    (

    custID

    int

    , eventName varchar(100)

    )

    insert

    into @customerEvent

    select

    1

    , 'No Show'

    union

    select

    2

    , 'No Show'

    union

    select

    2

    , 'Halloween'

    union

    select

    3

    , 'No Show'

    union

    select

    3

    , 'New Year Eve'

    union

    select

    3

    , 'xmas'

    union

    select

    4

    , 'July4th'

    Select

    * From @customerEvent

    ;

    with CTE AS (

    Select

    *,row_Number() over(Partition by custID order by eventname) as rn From @customerEvent

    )

    Select

    * From CTE where rn<>2

    Thanks

    Nandhu

    Thursday, October 25, 2012 4:23 AM
  • Try

    with set1 as
    (select custid,count(*)
    from @customerEvent
    where eventName<>'No Show'
    group by custid
    having count(*)>0)
    select *
    from @customerEvent
    where eventName<>'No Show'
    and custid in(select custid
    from set1);


    Many Thanks & Best Regards, Hua Min

    Thursday, October 25, 2012 4:25 AM
  • declare    @customerEvent    table
    (
              custID    int
            , eventName    varchar(100)
    )
    
    insert into @customerEvent
    select
    1, 'No Show'
    union
    select
    2, 'No Show'
    union
    select
    2, 'Halloween'
    union
    select
    3, 'No Show'
    union
    select
    3, 'New Year Eve'
    union
    select
    3, 'xmas'
    union
    select
    4, 'July4th'
    
    
    SELECT * FROM @customerEvent
    EXCEPT
    (
    SELECT custID
    	   ,a.eventName
    FROM
    	(
    	SELECT CustId,eventName,ROW_NUMBER() OVER(PARTITION BY custID ORDER BY custID) as rn 
    	FROM @customerEvent
    	) a
    WHERE a.rn <> 1 AND a.eventName LIKE 'No Show'
    )

    Output :

    custID	eventName
    1	No Show
    2	Halloween
    3	New Year Eve
    3	xmas
    4	July4th


    Narsimha

    • Marked as answer by light_wt Thursday, October 25, 2012 12:41 PM
    Thursday, October 25, 2012 4:33 AM
  •  ; with mycte as
     (
    SELECT custID, eventname,count(eventname) over(partition by custid) cnt from @customerEvent )
    select custID, eventname from mycte where  eventname<>'No Show' Or (eventname='No Show' and cnt=1)

    • Marked as answer by light_wt Thursday, October 25, 2012 12:44 PM
    Thursday, October 25, 2012 5:02 AM
  • teh example isn't the best. 

    "no show" doesn't always come as the second one. rn<>2 doesn't work all th etime.

    thanks.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, October 25, 2012 12:34 PM
  • i will need the eventname rather than the counts.  nice try.

    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, October 25, 2012 12:39 PM
  • thanks. narsimha!

    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, October 25, 2012 12:41 PM
  • yours is the fastest and most elegant query!!  thanks, Jingyang.

    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Thursday, October 25, 2012 12:46 PM
  • Can customer have several 'No Show' and no other shows? If yes, then the above solution will not work.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 25, 2012 12:51 PM
  • teh example isn't the best. 

    "no show" doesn't always come as the second one. rn<>2 doesn't work all th etime.

    thanks.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Exactly. You need explicitly express the ordering in ROW_NUMBER:

    ROW_NUMBER

    () OVER(PARTITION BY custID

    ORDER BY case eventName when 'No Show' then 2 else 1 end)


    Serg

    Thursday, October 25, 2012 12:59 PM
  • Try (this should work if one customer has multiple 'No Shows'):

     ; with cte as
     (
    SELECT custID, eventname, dense_rank() over(partition by custid
    order by case when EventName = 'No Show' then  2 else 1 end, EventName) as Rnk from @customerEvent )
    select custID, eventname from cte where  eventname<>'No Show' Or (eventname='No Show' and Rnk=1)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, October 25, 2012 1:04 PM
  • with

    cte as

    (

    select row_number()over(PARTITION by custID order by custid desc)as Rank1,* from customerEvent

    )

    --select * from cte

    delete

    from cte where rank1=2

    select

    * from customerEvent


    Vinodkumar.kapa

    Thursday, October 25, 2012 1:32 PM