The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
I've been working on this project to help point out any potential issue on payroll and I am having a hard time coming up with a solution.
I have 3 seperate tables that stores data related to a person, what they did for a day, and how it was accounted for in seperate systems. Theoretically speaking everything SHOULD match between all 3 tables but in the event it doesn't I want a report to point that out for me.
Here is a sample of the datasets.
Assignment Table
Date | Location Level 2 | Location Level 1 | personId | Work Performed | AssignmentID |
4/2/2018 0:00 | Location A | Location 1 | 11448 | Work Type 1 | aaaa1111 |
4/2/2018 0:00 | Location A | Location 1 | 11448 | Work Type 2 | aaaa1112 |
4/2/2018 0:00 | Location A | Location 1 | 16881 | Work Type 3 | aaaa1111 |
4/2/2018 0:00 | Location A | Location 1 | 18472 | Work Type 4 | aaaa1114 |
4/2/2018 0:00 | Location A | Location 1 | 21485 | Work Type 5 | aaaa1115 |
4/2/2018 0:00 | Location A | Location 1 | 21485 | Work Type 6 | aaaa1116 |
4/2/2018 0:00 | Location A | Location 1 | 21990 | Work Type 7 | aaaa1117 |
4/2/2018 0:00 | Location A | Location 1 | 22205 | Work Type 8 | aaaa1118 |
4/2/2018 0:00 | Location A | Location 1 | 23260 | Work Type 9 | aaaa1119 |
4/2/2018 0:00 | Location A | Location 1 | 21485 | Work Type 10 | aaaa1120 |
4/3/2018 0:00 | Location A | Location 1 | 31401 | Work Type 11 | aaaa1121 |
4/3/2018 0:00 | Location B | Location 1 | 16990 | Work Type 12 | aaaa1122 |
4/3/2018 0:00 | Location B | Location 1 | 17764 | Work Type 13 | aaaa1123 |
4/3/2018 0:00 | Location B | Location 1 | 17928 | Work Type 14 | aaaa1124 |
4/3/2018 0:00 | Location B | Location 1 | 18615 | Work Type 15 | aaaa1125 |
4/3/2018 0:00 | Location B | Location 1 | 19271 | Work Type 16 | aaaa1126 |
4/3/2018 0:00 | Location B | Location 1 | 21571 | Work Type 17 | aaaa1127 |
4/3/2018 0:00 | Location B | Location 1 | 21622 | Work Type 18 | aaaa1128 |
4/3/2018 0:00 | Location B | Location 1 | 21651 | Work Type 19 | aaaa1129 |
4/3/2018 0:00 | Location B | Location 1 | 22918 | Work Type 20 | aaaa1130 |
4/4/2018 0:00 | Location B | Location 1 | 22951 | Work Type 21 | aaaa1131 |
4/4/2018 0:00 | Location C | Location 2 | 11167 | Work Type 22 | aaaa1132 |
4/4/2018 0:00 | Location C | Location 2 | 11367 | Work Type 23 | aaaa1133 |
4/4/2018 0:00 | Location C | Location 2 | 12137 | Work Type 24 | aaaa1134 |
4/4/2018 0:00 | Location C | Location 2 | 12563 | Work Type 25 | aaaa1135 |
4/4/2018 0:00 | Location C | Location 2 | 22867 | Work Type 26 | aaaa1136 |
4/4/2018 0:00 | Location C | Location 2 | 28843 | Work Type 27 | aaaa1137 |
4/4/2018 0:00 | Location C | Location 2 | 14230 | Work Type 28 | aaaa1138 |
4/4/2018 0:00 | Location C | Location 2 | 17609 | Work Type 29 | aaaa1139 |
4/5/2018 0:00 | Location C | Location 2 | 17755 | Work Type 30 | aaaa1140 |
4/5/2018 0:00 | Location C | Location 2 | 18694 | Work Type 31 | aaaa1141 |
4/5/2018 0:00 | Location C | Location 2 | 14956 | Work Type 32 | aaaa1142 |
4/5/2018 0:00 | Location C | Location 2 | 17770 | Work Type 33 | aaaa1143 |
4/5/2018 0:00 | Location C | Location 2 | 14031 | Work Type 34 | aaaa1144 |
4/5/2018 0:00 | Location C | Location 2 | 14622 | Work Type 35 | aaaa1145 |
Payroll Table
personID | Earn Date | Hours |
395920 | 4/23/2018 | 8 |
395920 | 4/24/2018 | 8 |
395920 | 4/25/2018 | 8 |
395920 | 4/26/2018 | 8 |
395920 | 4/27/2018 | 8 |
395920 | 4/28/2018 | 8 |
489217 | 4/23/2018 | 8 |
489217 | 4/24/2018 | 8 |
489217 | 4/25/2018 | 8 |
489217 | 4/26/2018 | 8 |
489217 | 4/27/2018 | 8 |
502933 | 4/23/2018 | 8 |
502933 | 4/24/2018 | 8 |
502933 | 4/25/2018 | 8 |
502933 | 4/26/2018 | 8 |
502933 | 4/28/2018 | 8 |
505197 | 4/23/2018 | 8 |
505197 | 4/24/2018 | 8 |
505197 | 4/26/2018 | 8 |
505197 | 4/27/2018 | 8 |
505197 | 4/28/2018 | 8 |
1003651 | 4/23/2018 | 8 |
1003651 | 4/24/2018 | 8 |
1003651 | 4/25/2018 | 8 |
1003651 | 4/26/2018 | 8 |
1003651 | 4/28/2018 | 8 |
505935 | 4/23/2018 | 8 |
505935 | 4/24/2018 | 8 |
Secondary Data Table
AssignmentID | Hours |
aaaa1111 | 8 |
aaaa1112 | 8 |
aaaa1111 | 8 |
aaaa1114 | 8 |
aaaa1115 | 8 |
aaaa1116 | 8 |
aaaa1117 | 8 |
aaaa1118 | 8 |
aaaa1119 | 8 |
aaaa1120 | 8 |
aaaa1121 | 8 |
aaaa1122 | 8 |
aaaa1123 | 8 |
aaaa1124 | 8 |
aaaa1125 | 8 |
aaaa1126 | 8 |
aaaa1127 | 8 |
aaaa1128 | 8 |
aaaa1129 | 8 |
aaaa1130 | 8 |
aaaa1131 | 8 |
aaaa1132 | 8 |
aaaa1133 | 8 |
aaaa1134 | 8 |
aaaa1135 | 8 |
aaaa1136 | 8 |
aaaa1137 | 8 |
aaaa1138 | 8 |
aaaa1139 | 8 |
aaaa1140 | 8 |
The end goal is
I know there is alot of information here and I am forgetting alot but I am here to answer any questions.
@thmonte,
What is your expected result based on the above sample data?
I note that there is no matched personID in the first two tables. And you have many-to-many relationship among the three tables, do you create other bridge tables containing unique personID and unique AssignmentID to estabilish relationships among these tables?
Regards,
Lydia
@v-yuezhe-msft I do have a few other tables to create relationships
One table of unique personID
One table of unique Date
I do not have a table of unique AssignmentIDs but I suppose I can make one?
With that being said, the sample data was real data stripped of sensitive information. I did not create it with the intent of it actually producing results.
@thmonte,
Could you please share expected result based on the above sample data you post?
Regards,
Lydia
User | Count |
---|---|
160 | |
111 | |
96 | |
86 | |
75 |
User | Count |
---|---|
158 | |
136 | |
133 | |
81 | |
61 |