relating tables based on multiple column criteria

[GiantPygmy]

I have an issue of needing to get data from one table onto another based on multiple column conditions.   I have a Completed Tasks table that has the User ID of the person who completed the task, a Task Identifier Number, the date the task was created, and the date it was completed.  I then have a Users Table that has the User ID, The department they work for, a Begin Date they started that department, and an End Date for when they left that department.   The issue is that on the user table the User ID will be duplicated several times as a person has changed departments.  My ultimate goal is to have a way to track their on time completion (for example task completed in 1 day) based on department and not user.   Since a user can change departments I will have to account for what department that user was in at the time of completion of the task.

[v-danhe-msft]

Hi @GiantPygmy,

Could you please post me some sample data to have a test and your desired result?

Regards,

Daniel He

[GiantPygmy]

*UPDATE* lol NM, I figured out a solution (just to let you know i created a unique key based on user id and Date combo and tied it with that).  Feel silly now that I figured that out but couldn't find a way to delete the post.

Sorry about that.  Here are examples of tables I'm working with.

Task Table:

TaskID	UserID	CreateDate	CompleteDate
54631	634	5/1/2017	5/3/2017
54632	634	5/2/2017	5/2/2017
54632	281	8/18/2017	8/25/2017
54632	356	11/21/2017	12/3/2017
54632	281	12/1/2017	12/17/2017
54632	634	1/7/2018	1/8/2018
54632	356	2/13/2018	2/28/2018

User Table:

*Note* I do have a another table like this but instead of a StartDate and EndDate it has a single Date column with all the dates inbetween listed individually.

UserID	Department	StartDate	EndDate
634	Warehouse	4/1/2017	12/1/2017
634	Clerk	12/2/2017	5/1/2018
281	Floor	4/1/2017	12/1/2017
281	Warehouse	12/2/2017	3/1/2018
281	Clerk	3/2/2018	5/1/2018
356	Clerk	6/1/2017	1/1/2018
356	Floor	1/2/2018	5/1/2018

Ultimate goal is to have it display the total tasks completed by each department by Year and Month:

	Mar 17	Aug 17	Dec 17	Jan 18	Feb 18
Warehouse	2	0	1	0	0
Floor	0	1	0	0	1
Clerk	0	0	1	1	0