CODEWITHSUNDEEP
sqlsql-serversql-server-2012
andres
andres

Reputation: 51

Filter data by names in common to a job SQL

Hi guys I currently am dealing with an issue and I want to see if there is a way to do this

A staff member wants to see a report that shows the number of hours she has in all of the jobs she worked on, plus the hours for all other staff who also worked on those jobs. So if my data looks like this: 

Client-----     Job------   Staff------     Hours    
A-----------    1 --------  Caty----------  15    
A-----------    1 --------  John----------  10    
A-----------    1 --------  Greg----------  6     
B-----------    2 --------  Caty----------  8    
B-----------    2 --------  Ralph-------    10    
B-----------    2 --------  Derek-------    12     
C-----------    1 --------  Steve -------   9      
C-----------    1 --------  Bill----------  20     
C-----------    1 --------  Mike--------    18

For Caty, the report should give me this: 

Client-----     Job------   Staff------     Hours    
A-----------    1 --------  Caty----------  15    
A-----------    1 --------  John----------  10    
A-----------    1 --------  Greg----------  6     
B-----------    2 --------  Caty----------  8    
B-----------    2 --------  Ralph-------    10    
B-----------    2 --------  Derek-------    12

I cannot put in the WHERE clause “WHERE Staff=’Caty’” because that would filter out John, Greg, Ralph and Derek, leaving me with only Caty’s hours. 

select clientcode, clientname, job_name, staffcode, sum(wiphours) as Hours
from tbltranwip w
inner join tblengagement e on e.ContIndex=w.ContIndex
inner join tblJob_Header h on h.Job_Idx=w.ServPeriod
inner join tblstaff s on s.StaffIndex=w.StaffIndex
where wipdate between 'jul 1 2015' and 'jul 31 2015' and TransTypeIndex=1 
and w.ContIndex<900000 and staffcode = 'Caty'
group by clientcode, clientname, job_name, staffcode

That code only show's me Caty :S

Upvotes: 0

Views: 132

Answers (4)

zedfoxus
zedfoxus

Reputation: 37099

Assuming your table or view looked like this:

CREATE TABLE job(
    Client char(1) NOT NULL,
    Job int NOT NULL,
    Staff varchar(20) NOT NULL,
    Hours int NOT NULL
);

insert into job values
('A',1, 'Caty', 15),
('A',1, 'John', 10),
('A',1, 'Greg', 6),
('B',2, 'Caty', 8),
('B',2, 'Ralph', 10),
('B',2, 'Derek', 12),
('C',1, 'Steve', 9),
('C',1, 'Bill', 20),
('C',1, 'Mike', 18);

You could write a query like so to retrieve desired information:

select client, job, staff, sum(hours) as totalhours
from job x
where exists (
    select 1 
    from job 
    where staff = 'Caty'
    and client = x.client
    and job = x.job)
group by client, job, staff

Result:

client job         staff                totalhours
------ ----------- -------------------- -----------
A      1           Caty                 15
A      1           Greg                 6
A      1           John                 10
B      2           Caty                 8
B      2           Derek                12
B      2           Ralph                10

SQLFiddle example: http://sqlfiddle.com/#!6/88075/1

Possibly try this to resolve your issue

with data as (
    select clientcode, clientname, job_name, staffcode, sum(wiphours) as Hours
    from tbltranwip w
    inner join tblengagement e on e.ContIndex=w.ContIndex
    inner join tblJob_Header h on h.Job_Idx=w.ServPeriod
    inner join tblstaff s on s.StaffIndex=w.StaffIndex
    where wipdate between 'jul 1 2015' and 'jul 31 2015' and TransTypeIndex=1 
    and w.ContIndex<900000
    group by clientcode, clientname, job_name, staffcode
)

select clientcode, job_name, staffcode, sum(wiphours) as Hours
from data x
where exists (
    select 1 
    from data
    where staffcode = 'Caty'
    and clientcode= x.clientcode
    and job_name= x.job_name)
group by clientcode, job_name, staffcode

Upvotes: 1

Ashish
Ashish

Reputation: 131

Try this in your where clause: staffcode = 'Caty' or StaffCode in (select staffers from the same group as Caty)

Upvotes: 0

jpw
jpw

Reputation: 44891

To get the staff that worked on the same jobs as Caty you'll want to use the in predicate with a subquery; something like

where ...
and job in (select job from appropriate_table where Staff='Caty')

I'm not sure how your tables relate so you'll want to adjust the code as needed, but it should give you an idea.

Upvotes: 0

Luc
Luc

Reputation: 1491

You could select the jobs caty worked in, in a subquery, which you join the get the result

Select sum(hours) from timetable t
inner join (select client, job from timetable where staff = 'caty') as sub 
on t.client = sub.client and t.job = sub.job

This should get you started

Upvotes: 0

Related Questions