Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |