June 19, 2013 at 12:50 pm
I have a scenario where a single client can have multiple workers with either all workers having same rate or each worker having different rates for the same participant,
sample data is as below
worker client rate
frank derek 0.55
sandra derek 0.55
saeed haleema 0.555
curtis julia 0.555
marilyn julia 0.55
jane william 0.555
adam william 0.55
lisa anderson 0.555
marketa pamela 0.55
Now i need to get the list of clients and workers who have different rates like for example result should be
curtis julia 0.555
marilyn julia 0.55
jane william 0.555
adam william 0.55
June 19, 2013 at 1:10 pm
It's quite easy to accomplish this task. The slowest part is to recreate the DDL and sample data. This time I did it on a CTE, but you should take a look at the article linked in my signature for better and faster help.
Remember to understand what the query is doing and ask if you don't.
WITH Workers AS(
SELECT * FROM( VALUES
('frank', 'derek', 0.55),
('sandra', 'derek', 0.55),
('saeed', 'haleema', 0.555),
('curtis', 'julia', 0.555),
('marilyn', 'julia', 0.55),
('jane', 'william', 0.555),
('adam', 'william', 0.55),
('lisa', 'anderson', 0.555),
('marketa', 'pamela', 0.55))x(worker, client, rate))
SELECT *
FROM Workers
WHERE client IN( SELECT w.client
FROM Workers w
GROUP BY w.client
HAVING COUNT( DISTINCT w.rate) > 1)
June 19, 2013 at 1:22 pm
Thanks a lot.
It worked perfectly!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply