October 28, 2018 at 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'
October 29, 2018 at 1:08 am
texpic - Sunday, October 28, 2018 5:24 PMFor 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
October 29, 2018 at 4:44 am
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).
October 29, 2018 at 8:59 am
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 pleft 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/
October 29, 2018 at 9:43 am
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
October 29, 2018 at 10:09 am
texpic - Sunday, October 28, 2018 5:24 PMFor 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
October 29, 2018 at 10:11 am
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply