Identify Deals Sold by W2 Employees Only

  • texpic

    SSCertifiable

    Points: 5882

    For this example assume a company has both W2 and commission employees. What I'm trying to identify is sales record that are W2 only. To keep this example simple females are the W2 and males the commission.  In this example only DealId 4 and 5 were all W2. The real data set has over 20 W2 and over 100 commission so putting together a list of commission is harder than W2. My mind is a blank on how to write a query to accomplish this. Thanks for any suggestions.


    CREATE TABLE #W2 (W2Names varchar(30))
    INSERT INTO #W2
    SELECT 'Ann' UNION ALL
    SELECT 'Barb' UNION ALL
    SELECT 'Carla' UNION ALL
    SELECT 'Dana' UNION ALL
    SELECT 'Ellen'

    CREATE TABLE #Paid
    (DealId int, Payee varchar(30))
    INSERT INTO #Paid
    SELECT 1, 'Ann' UNION ALL
    SELECT 1, 'Barb' UNION ALL
    SELECT 1, 'Joe' UNION ALL
    SELECT 1, 'Jim' UNION ALL
    SELECT 2, 'Ann' UNION ALL
    SELECT 2, 'Joe' UNION ALL
    SELECT 2, 'Jim' UNION ALL
    SELECT 3, 'Ann' UNION ALL
    SELECT 3, 'Joe' UNION ALL
    SELECT 4, 'Ann' UNION ALL
    SELECT 4, 'Barb' UNION ALL
    SELECT 5, 'Ann' UNION ALL
    SELECT 5, 'Barb' UNION ALL
    SELECT 5, 'Dana' UNION ALL
    SELECT 6, 'Joe' UNION ALL
    SELECT 6, 'Jim'

  • saravanatn

    SSCarpal Tunnel

    Points: 4530

    texpic - Sunday, October 28, 2018 5:24 PM

    For this example assume a company has both W2 and commission employees. What I'm trying to identify is sales record that are W2 only. To keep this example simple females are the W2 and males the commission.  In this example only DealId 4 and 5 were all W2. The real data set has over 20 W2 and over 100 commission so putting together a list of commission is harder than W2. My mind is a blank on how to write a query to accomplish this. Thanks for any suggestions.


    CREATE TABLE #W2 (W2Names varchar(30))
    INSERT INTO #W2
    SELECT 'Ann' UNION ALL
    SELECT 'Barb' UNION ALL
    SELECT 'Carla' UNION ALL
    SELECT 'Dana' UNION ALL
    SELECT 'Ellen'

    CREATE TABLE #Paid
    (DealId int, Payee varchar(30))
    INSERT INTO #Paid
    SELECT 1, 'Ann' UNION ALL
    SELECT 1, 'Barb' UNION ALL
    SELECT 1, 'Joe' UNION ALL
    SELECT 1, 'Jim' UNION ALL
    SELECT 2, 'Ann' UNION ALL
    SELECT 2, 'Joe' UNION ALL
    SELECT 2, 'Jim' UNION ALL
    SELECT 3, 'Ann' UNION ALL
    SELECT 3, 'Joe' UNION ALL
    SELECT 4, 'Ann' UNION ALL
    SELECT 4, 'Barb' UNION ALL
    SELECT 5, 'Ann' UNION ALL
    SELECT 5, 'Barb' UNION ALL
    SELECT 5, 'Dana' UNION ALL
    SELECT 6, 'Joe' UNION ALL
    SELECT 6, 'Jim'

    How I can conclude that only DealId 4 and 5 were all W2? Is there any rules for it?

    Saravanan

  • texpic

    SSCertifiable

    Points: 5882

    Table #W2 is the list of all W2 employees.  4 and 5 only have employees from that table.  All other tables have employees who are not in the #W2 table (1,2,3,and 6).

  • LinksUp

    SSCertifiable

    Points: 6495


    You just want ID's that are all W2's? Is that correct?

    Is this what you are trying to do or is it more complicated than this?

    select p.DealID, p.Payee, case when w.W2Names is NULL then 'Commission' else 'W2 Emp' end [PayStatus]
    from #paid p
    left join #w2 w
    on w.w2Names = p.Payee

    select * from #paid p
    where not exists (select 1 from #w2 w where w.w2names = p.Payee)

    select * from #paid p
    where exists (select 1 from #w2 w where w.w2names = p.Payee)

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • texpic

    SSCertifiable

    Points: 5882

    LinksUp, very close.  Taking what you did and modifying it a bit gets the desired result.  Thank you.

    select DISTINCT p.DealID, case when w.W2Names is NULL then 'Commission' else 'W2 Emp' end [PayStatus]

    into #A from #paid p

    left join #w2 w

    on w.w2Names = p.Payee

    DELETE A

    FROM #A A

    JOIN (SELECT * FROM #A WHERE PayStatus = 'Commission') B

    ON A.DealId = B.DealId

    SELECT * FROM #A

  • drew.allen

    SSC Guru

    Points: 76737

    texpic - Sunday, October 28, 2018 5:24 PM

    For this example assume a company has both W2 and commission employees. What I'm trying to identify is sales record that are W2 only. To keep this example simple females are the W2 and males the commission.

    This seems gratuitously sexist.  W2 employees are differentiated by being listed in the W2 table.  PERIOD.  The required solution can be reached without ever referencing gender, so it's very odd that you would mention it or think that doing so would keep it "simpler".  It's also odd that you chose to make the females W2 and the males commission implying females are incapable of making a living if you don't prop them up with a guaranteed salary.

    I'm fairly sure that you did not intend to make sexist remarks, but part of the reason that problems like this persist is that people don't see it as being harmful.  This makes it harder for people to speak up, because others think they're being too sensitive.  I'm here to say that your example is harmful.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • John Mitchell-245523

    SSC Guru

    Points: 148761

    I always wince when I see stuff inserted into a staging table, only to be immediately deleted.  It's usually more efficient not to insert it in the first place.  Try this:

    WITH Statuses AS (
        SELECT
            p.DealID
        ,   CASE
                WHEN w.W2Names IS NULL THEN 'Commission'
                ELSE 'W2 Emp'
            END AS PayStatus
        FROM #paid p
        LEFT JOIN #w2 w
        ON w.w2Names = p.Payee
        )
    SELECT
        DealID
    ,   MAX(PayStatus) AS PayStatus
    FROM Statuses
    GROUP BY DealID
    HAVING MIN(PayStatus) = 'W2 Emp';

    Don't take my word for it, though.  Test on a large data set and see which is more efficient.  My solution touches each source table only once, although it does involve a potentially expensive sort operation, so you'll want to make sure you have the right indexes if you decide to use it.

    John

Viewing 7 posts - 1 through 7 (of 7 total)

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