[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

Query Find who employees worked with most often during a specific time period.

We are trying to find who employee's worked with on client projects most often.  This will allow us to assign co-workers for Peer Review.

The query would have to look at all billable client work from the WIP table for each client and employee during a specific time period.  Then figure out the top 5 people this employee worked with during this period.

I'm not concerned about the client name or the work detail.  Just the name of the employee and the top 5 co-workers based on the amount of time spent on the client work for the time period.
The query should not included the original employee as a top 5 co-worker.  I know that seems obvious but I want to be sure.

   
Client Table:

Field             Type              Description
-----             ----              -----------
ID                Long              Unique Client ID (KEY)


Employee Table:

Field             Type              Description
-----             ----              -----------
ID		Long		Unique Employee ID (KEY)
Empnum		Text		Employee Number 
Empfname	         Text		Employee First Name
Emplname	         Text		Employee Last Name
Empstatus	         Text		Employee Status A=Active, I=Inactive


WIP Table (Work In Progress /Billable Items / Work Performed):

Field             Type              Description
-----             ----              -----------
ID		Long		WIP Unique ID
WcltID		Long		Client ID (client.ID)
WempID		Long		Employee ID (employee.ID)
Wdate		Date/Time 	Transaction Date
Wbillable 	Byte		Billable=1, Nonbillable=0
Whours		single		Hours

Open in new window


Sample Output:

Employee Name: Jane Doe
  Top 5 Co-workers
                            Sam Smith
                            Carla Jones
                            Jamie Conrad
                            Valerie Simms
                            Mark Jenkins

I hope this makes sense.
0
ITMcmcpa
Asked:
ITMcmcpa
  • 14
  • 11
  • 2
1 Solution
 
Chandan_GowdaCommented:
Try the attached query... I am considering all billable and Active employees only

Change the date range according to your requirement
SELECT TOP 5 *  FROM 
	(SELECT
			 A.Empnum
			,A.Empfname
			,A.Emplname 
			,CONVERT(VARCHAR,Wdate,101) AS TransactionDate
			,SUM(Whours) AS TotalHrsWorked
FROM Employee A
	INNER JOIN WIP B 
			INNER JOIN Client C ON B.WcltID	=C.ID
	On A.ID=B.WempID 
WHERE A.Empstatus='A' AND B.Wbillable=1 
AND (CONVERT(VARCHAR,Wdate,101)>='1/1/2011' AND CONVERT(VARCHAR,Wdate,101)<='1/10/2011')
GROUP BY  A.Empnum
		 ,A.Empfname
		 ,A.Emplname 
		 ,CONVERT(VARCHAR,Wdate,101)) Z
ORDER BY TotalHrsWorked DESC

Open in new window

0
 
ITMcmcpaAuthor Commented:
This just gives me the top 5 employees based on hours worked.

I would like to see all employees that have billable time based on client work along with up to the top 5 other employees working on that clients during the same time frame.

So, let's say from 1/1/2011 to 4/1/2011 Jane Doe worked on 16 different client projects.  She may have 100 WIP billable items in the table during this time frame.  During this same time, Sam Smith and Carla Jones worked on some of the same clients.  So Sam and Carla have billable WIP items in the table for the same clients as Jane Doe during the same time frame.  Many other employees may have billable WIP items for the same clients as well.

What I would like to see is: For Jane Doe, who were the top 5 co-workers that worked with her during that timeframe based on Whours?

They would have to work on the same clients during the same period.



0
 
ralmadaCommented:
From the table definitions, it looks like you're using MS-Access. If not, please clarify that what database you're using.

In the mean time try the attached. If this doesn't work please post some sample data (input) for the output you've mentioned above. If you can upload a sample database even better.
select t1.Employee, a.wEmpid
from (Employee as t1
left join (
	select * from WIP as a 
	where a.wEmpID in (
			select top 5 b.WempID 
			from (select WempID, WcltID, sum(Hours) as cnt from WIP where wDate between #...# and #...# group by wEmpid, WcltID) as b 
			where b.wempID > a.EmpID and b.WcltID = a.wcltID
			order by cnt desc
		)
	) as t2 on t1.EmpID = t1.ID)

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
ITMcmcpaAuthor Commented:
It is SQL 2008.  It just took those definitions from a document provided by the software company.  Those aren't all of the fields in the tables, just what I thought would be relevant.  I will try your query.
0
 
ralmadaCommented:
if it's SQL 2008, then you can try the below
;with CTE as (
	select 	WempID, 
		WcltID, 
		sum(Hours) as cnt 
	from WIP 
	group by wEmpid, WcltID
	where WDate between '01/01/2011' and '01/04/2011' --change period accordingly
), CTE2 as (
	select *, row_number() over (partition by WcltID order by cnt desc) rn from CTE
)
select a.*, b.* 
from Employee a
left join CTE2 b on a.ID = b.wEmpID and b.rn <= 5

Open in new window

0
 
ITMcmcpaAuthor Commented:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'where'.
0
 
ralmadaCommented:
sorry,

inverted lines there
;with CTE as (
	select 	WempID, 
		WcltID, 
		sum(Hours) as cnt 
	from WIP 
	where WDate between '01/01/2011' and '01/04/2011' --change period accordingly
	group by wEmpid, WcltID
), CTE2 as (
	select *, row_number() over (partition by WcltID order by cnt desc) rn from CTE
)
select a.*, b.* 
from Employee a
left join CTE2 b on a.ID = b.wEmpID and b.rn <= 5

Open in new window

0
 
ITMcmcpaAuthor Commented:
This seems to be getting close, I think.  It looks like the query is pulling the Top 5 WIP records (billable time).  What I would like to see is the following:

Employee Jane Doe -> Look at who she worked with by billable time during that period.  From that list, narrow it down to the top 5 co-workers that she worked with.
Sample Output:

Employee Name: Jane Doe
  Top 5 Co-workers
                            Sam Smith
                            Carla Jones
                            Jamie Conrad
                            Valerie Simms
                            Mark Jenkins
Or
Jane Doe: Sam Smith, Carla Jones, Jamie Conrad, Valerie Simms, Mark Jenkins

0
 
ralmadaCommented:
>.It looks like the query is pulling the Top 5 WIP records (billable time).  <<
Did you try the query?
0
 
ITMcmcpaAuthor Commented:
Yes, I changed it a bit so that it didn't pull unnecessary records.

Sample output:
EmpName      empnum   WempID WcltID  cnt   rn
Doe, Jane      2285        344        2741     .25   3
Smith, Peter   2004        4            2754   1.00   1
Carter, Sam   2174        151        2755   4.00   1
Smith, Peter   2004         4           2755   2.10    3

etc..
0
 
ralmadaCommented:
Could you please provide some sample data? I mean the input to the example above? as well as explain what's wrong with the output above?
0
 
ITMcmcpaAuthor Commented:
I do not know for certain that there is anything wrong with the raw data output.  At first glance it looks like it is ranking the top 5 WIP entries by employee.  I need it to iterate through each employee and find the top 5 employees that have WIP with the same client.  Then get data where I can obtain the output desired in message # ID: 35474734.  It is highly likely that I am reading the query incorrectly and you are providing me what I need.  I just need to figure out the logical method to get the desired output.

I will see what I can do to get you relevant sample data.  That may prove to be difficult.  
0
 
ralmadaCommented:
ok, The reason I'm asking for some sample data is so that I can reproduce your environment and provide you with an exact query. The data doesn't have to be real data, but it should be representative enough so that your scenario can be recreated.
0
 
ITMcmcpaAuthor Commented:
I believe that I have included enough rows and fields to accomplish the task.

 project1.zip
0
 
ralmadaCommented:
There's something not clear to me.

>>Employee Jane Doe -> Look at who she worked with by billable time during that period.  From that list, narrow it down to the top 5 co-workers that she worked with.<<

How do you determine the top5 co-workers? Can you let me know for client #1 who will be the top 5 co-workers


In the meantime let's start with the query attached below.


;with CTE as (
select WempID, WcltID, sum(WHours) as cnt from WIP$ 
where WempID <> 0
group by wEmpid, WcltID
), CTE2 as (
select *, row_number() over (partition by WempID order by cnt desc) rn from CTE
)
select  * from CTE2 a
cross apply (select * from CTE2 b where b.WempID > a.WempID and a.WcltID = b.WcltID and b.rn <= 5) t1
where a.rn <= 5 
order by a.WempID, a.cnt desc, t1.cnt desc

Open in new window

0
 
ITMcmcpaAuthor Commented:
First it would have to be determined which clients the employee worked on during the time period.

Hypothetical:
Jane Doe worked the with the following clients during the month of January –

23124: 5 Hours
86483: 10 Hours
68954: 2 Hours
77843: 3 Hours
63254: 1.5 Hours

Now we need to find all of the other employees that worked the same clients in January –

Peter Smith

23124: 2 Hours Yes worked together
12321: 5 Hours No did not work together
68954: 3 Hours Yes worked together
34233: 4 Hours No did not work together
Yes worked together total:  5 Hours

Sam Carter

86483: 6 Hours Yes worked together
90323: 12 Hours No did not work together
64524: 2.5 Hours Yes worked together
Yes worked together: 8.5 Hours

Nick Samuals

68787: 5 Hours No did not work together
39483: 8 Hours No did not work together
32342: 9 Hours No did not work together

Yes worked together: 0 Hours

Haley Marcum

23124: 6 Hours Yes worked together
68954: 3 Hours Yes worked together
88865: 5 Hours No did not work together
Yes worked together: 9 Hours

So the Top 2 (to keep the scenario short) are:
Sam Carter and Haley Marcum
Output:
Jane Doe: Sam Carter, Haley Marcum

Move to the next employee in the database and make the same comparison until all employees have been matched with their top coworkers.  Some employees are not billable so they will have no (null) matches.

Now some assumptions are made because these jobs are not project based.  You will see that Jane Doe may not have worked as many hours as some of the other employees at a specific client.  There simply isn’t enough information in the database to determine if they were together more than the billable time.  We are assuming if there is billable time for the same client during the period that they worked together.  There is not another identifier that tells us they worked together. The way our jobs work it is fairly safe to assume they were together more than what is showing in the database.  

We are just trying to determine which employees were together the most during the period.  The more hours that shows for the coworker that worked on the same client determines the ranking.  
The coworkers that are matched to the employee will perform a review.

I’m not sure if I can get to manually calculating a live example today.  But I will try.




0
 
ITMcmcpaAuthor Commented:
Looks like I highlighted a client number for Sam Carter that did not match one of Jane Doe's clients.  Sorry about that.  Let's pretend they match. ;-)
0
 
Chandan_GowdaCommented:
I have modified the query. Please try and let me know

SELECT * FROM(
SELECT  *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY TotalHrsWorked desc ) AS Result  FROM 
	(

SELECT 
			 A.Empnum
			,A.Empfname
			,A.Emplname 
			--,CONVERT(VARCHAR,Wdate,101) AS TransactionDate
			,c.ID	
			,SUM(Whours) AS TotalHrsWorked
FROM Employee A
	INNER JOIN WIP B 
			INNER JOIN Clients C ON B.WcltID=C.ID
	On A.ID=B.WempID 
WHERE A.Deleted=0 AND B.Wbillable=1 
--AND (CONVERT(VARCHAR,Wdate,101)>='1/14/2011' AND CONVERT(VARCHAR,Wdate,101)<='1/26/2011') --CHANGE THIS ACCORDINGLY
GROUP BY  A.Empnum
		 ,A.Empfname
		 ,A.Emplname 
		 --,CONVERT(VARCHAR,Wdate,101)
		 ,c.id

) Z)X
where  Result<=5 --and ID=24322
ORDER BY ID,Result ASC

Open in new window

0
 
ralmadaCommented:
I believe Jane and Peter worked together 4 hours not 5 as you mentioned, and Jane and Sam did it for 7.5 hours not 8.5, because one charged more hours than the other to the client, what you think?

If we assume that, then try this query
;with CTE as (
	select WempID, WcltID, sum(WHours) as cnt from WIP$ 
	where WempID <> 0 and WDate between '01/01/2011' and '01/04/2011' and Wbillable = 1
	group by wEmpid, WcltID
), CTE2 as (
	select a.WempID, b.wempid as coworker, sum(case when a.cnt < b.cnt then a.cnt else b.cnt end) as shours
	from CTE a
	inner join CTE b on a.wempID <> b.wempid and a.wcltID = b.wcltID
	group by a.WempID, b.wempid
), CTE3 as (
	select *, row_number() over (partition by WempID order by shours desc) rn from 
	FROM CTE2
)
select WempID, coworker, shours
from CTE3
where rn <= 5

Open in new window

0
 
ralmadaCommented:
Now if you are going to rank them based on the time the coworked charged to the client in common, then simply change it to:
;with CTE as (
	select WempID, WcltID, sum(WHours) as cnt from WIP$ 
	where WempID <> 0 and WDate between '01/01/2011' and '01/04/2011' and Wbillable = 1
	group by wEmpid, WcltID
), CTE2 as (
	select a.WempID, b.wempid as coworker, sum(b.cnt) as shours
	from CTE a
	inner join CTE b on a.wempID <> b.wempid and a.wcltID = b.wcltID
	group by a.WempID, b.wempid
), CTE3 as (
	select *, row_number() over (partition by WempID order by shours desc) rn from 
	FROM CTE2
)
select WempID, coworker, shours
from CTE3
where rn <= 5

Open in new window

0
 
ITMcmcpaAuthor Commented:
@ralmada.  I think this is it.  Is there an easy way to get the names in the output for both the emloyee and the coworker?
0
 
ralmadaCommented:
and to add the Employees name see the same two alternatives updated below:
--First alternative
;with CTE as (
	select a.WempID, b.EmpFName, b.EmpLName, a.WcltID, sum(a.WHours) as cnt 
	from WIP a 
	inner join Employee b on a.WempID = b.ID
	where a.WempID <> 0 and a.WDate between '01/01/2011' and '01/04/2011' and a.Wbillable = 1
	group by a.wEmpid, b.EmpFName, b.EmpLName, a.WcltID
), CTE2 as (
	select a.WempID, a.EmpFName, a.EmpLName, b.wempid as coworker, b.EmpFName as CoFName, b.EmpLName as CoLName, sum(case when a.cnt < b.cnt then a.cnt else b.cnt end) as shours
	from CTE a
	inner join CTE b on a.wempID <> b.wempid and a.wcltID = b.wcltID
	group by a.WempID, a.EmpFName, a.EmpLName, b.wempid, b.EmpFName, b.EmpLName
), CTE3 as (
	select *, row_number() over (partition by WempID order by shours desc) rn from 
	FROM CTE2
)
select WempID, EmpFName, EmpLName, coworker, CoFNmae, CoLName, shours
from CTE3
where rn <= 5


--second alternative
;with CTE as (
	select a.WempID, b.EmpFName, b.EmpLName, a.WcltID, sum(a.WHours) as cnt 
	from WIP a 
	inner join Employee b on a.WempID = b.ID
	where a.WempID <> 0 and a.WDate between '01/01/2011' and '01/04/2011' and a.Wbillable = 1
	group by a.wEmpid, b.EmpFName, b.EmpLName, a.WcltID
), CTE2 as (
	select a.WempID, a.EmpFName, a.EmpLName, b.wempid as coworker, b.EmpFName as CoFName, b.EmpLName as CoLName, sum(b.cnt) as shours
	from CTE a
	inner join CTE b on a.wempID <> b.wempid and a.wcltID = b.wcltID
	group by a.WempID, a.EmpFName, a.EmpLName, b.wempid, b.EmpFName, b.EmpLName
), CTE3 as (
	select *, row_number() over (partition by WempID order by shours desc) rn from 
	FROM CTE2
)
select WempID, EmpFName, EmpLName, coworker, CoFNmae, CoLName, shours
from CTE3
where rn <= 5

Open in new window

0
 
ITMcmcpaAuthor Commented:
Something is really different about those two alt queries.  The shours are not calculated the same so I get different results between them.
0
 
ralmadaCommented:
yes,

The first one gets the hours based on the time they spent together so if the employee charged 1.5 and the coworker charged 3 hours for the same client, it will consider 1.5 only:

sum(case when a.cnt < b.cnt then a.cnt else b.cnt end)

Please see example attached below.
 
The second one get the total hours charged by the coworker only:
sum(b.cnt)

use the one you deem fit for your need.
Assuming 1 = Jane, 2 = Peter and 3 = Sam then
1  5   23124 2  2
1  2   68954 2  3
------------------
1  7         2  5         4 (2 + 2) <-- Hours worked together

1  10  86483 3  6
1  1.5 63254 3  2.5
--------------------   
1  11.5      3   8.5     7.5 (6 + 1.5) <-- Hours worked together

Open in new window

0
 
ITMcmcpaAuthor Commented:
Got it.  I will get back with you shortly.
0
 
ITMcmcpaAuthor Commented:
Wonderful as usual!  I will post what I used as the final.  Thanks again!
0
 
ITMcmcpaAuthor Commented:
Here is the final query that I used in Report Builder:

 
;with CTE as (
	select a.WempID, b.EmpFName, b.EmpLName, a.WcltID, sum(a.WHours) as cnt 
	from WIP a 
	inner join Employee b on a.WempID = b.ID
	where a.WempID <> 0 and a.WDate between @begdate and @enddate and a.Wbillable = 1
	group by a.wEmpid, b.EmpFName, b.EmpLName, a.WcltID
), CTE2 as (
	select a.WempID, a.EmpFName, a.EmpLName, b.wempid as coworker, b.EmpFName as CoFName, b.EmpLName as CoLName, sum(case when a.cnt < b.cnt then a.cnt else b.cnt end) as shours
	from CTE a
	inner join CTE b on a.wempID <> b.wempid and a.wcltID = b.wcltID
	group by a.WempID, a.EmpFName, a.EmpLName, b.wempid, b.EmpFName, b.EmpLName
), CTE3 as (
	select *, row_number() over (partition by WempID order by shours desc) rn
	FROM CTE2
)
select WempID, EmpLName + ', ' + EmpFName as EmployeeName, coworker, CoLName + ', ' + CoFName as CoWorkName, shours
from CTE3
where rn <= @Top

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 14
  • 11
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now