Forum Discussion
Create Discrepancy Report from Different Tables
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
- There can be multiple 'Work Performed' per personID per day
- If there are multiple assigments and depending on the 'Location Level 2' there is an "expected" amount of hours payed
- For example, if personId has two specfic "Work Performed" and their Location is Location A then Pay Amount should be "X
- I would have to create a calculated column based on the above scenarios to come up with this "Pay Amount" column
| 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 will match multiple records in the Assignment Table
- There will be one record per Earn Date per personID
- Hours should match the calculated column 'Pay Amount' in Assignment 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
- The assignmentID will match an assignmentID in the AssignmentID
- There could be more than one record for each AssignmentID
- In the event there is more than one, we can SUM them
- The SUM of Hours per assignmentID should match 'Hours' in payroll table, and 'Pay Amount' from Assignment 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
- To see any discrepancies between the hours in all 3 tables
- If there is a discrepancies I need to see what the 'Work Performed' was
I know there is alot of information here and I am forgetting alot but I am here to answer any questions.
3 Replies
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,
LydiaAnonymous 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
