Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
thmonte
Helper IV
Helper IV

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

 

DateLocation Level 2Location Level 1personIdWork PerformedAssignmentID
4/2/2018 0:00Location ALocation 111448Work Type 1aaaa1111
4/2/2018 0:00Location ALocation 111448Work Type 2aaaa1112
4/2/2018 0:00Location ALocation 116881Work Type 3aaaa1111
4/2/2018 0:00Location ALocation 118472Work Type 4aaaa1114
4/2/2018 0:00Location ALocation 121485Work Type 5aaaa1115
4/2/2018 0:00Location ALocation 121485Work Type 6aaaa1116
4/2/2018 0:00Location ALocation 121990Work Type 7aaaa1117
4/2/2018 0:00Location ALocation 122205Work Type 8aaaa1118
4/2/2018 0:00Location ALocation 123260Work Type 9aaaa1119
4/2/2018 0:00Location ALocation 121485Work Type 10aaaa1120
4/3/2018 0:00Location ALocation 131401Work Type 11aaaa1121
4/3/2018 0:00Location BLocation 116990Work Type 12aaaa1122
4/3/2018 0:00Location BLocation 117764Work Type 13aaaa1123
4/3/2018 0:00Location BLocation 117928Work Type 14aaaa1124
4/3/2018 0:00Location BLocation 118615Work Type 15aaaa1125
4/3/2018 0:00Location BLocation 119271Work Type 16aaaa1126
4/3/2018 0:00Location BLocation 121571Work Type 17aaaa1127
4/3/2018 0:00Location BLocation 121622Work Type 18aaaa1128
4/3/2018 0:00Location BLocation 121651Work Type 19aaaa1129
4/3/2018 0:00Location BLocation 122918Work Type 20aaaa1130
4/4/2018 0:00Location BLocation 122951Work Type 21aaaa1131
4/4/2018 0:00Location CLocation 211167Work Type 22aaaa1132
4/4/2018 0:00Location CLocation 211367Work Type 23aaaa1133
4/4/2018 0:00Location CLocation 212137Work Type 24aaaa1134
4/4/2018 0:00Location CLocation 212563Work Type 25aaaa1135
4/4/2018 0:00Location CLocation 222867Work Type 26aaaa1136
4/4/2018 0:00Location CLocation 228843Work Type 27aaaa1137
4/4/2018 0:00Location CLocation 214230Work Type 28aaaa1138
4/4/2018 0:00Location CLocation 217609Work Type 29aaaa1139
4/5/2018 0:00Location CLocation 217755Work Type 30aaaa1140
4/5/2018 0:00Location CLocation 218694Work Type 31aaaa1141
4/5/2018 0:00Location CLocation 214956Work Type 32aaaa1142
4/5/2018 0:00Location CLocation 217770Work Type 33aaaa1143
4/5/2018 0:00Location CLocation 214031Work Type 34aaaa1144
4/5/2018 0:00Location CLocation 214622Work Type 35aaaa1145

 

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

 

personIDEarn DateHours
3959204/23/20188
3959204/24/20188
3959204/25/20188
3959204/26/20188
3959204/27/20188
3959204/28/20188
4892174/23/20188
4892174/24/20188
4892174/25/20188
4892174/26/20188
4892174/27/20188
5029334/23/20188
5029334/24/20188
5029334/25/20188
5029334/26/20188
5029334/28/20188
5051974/23/20188
5051974/24/20188
5051974/26/20188
5051974/27/20188
5051974/28/20188
10036514/23/20188
10036514/24/20188
10036514/25/20188
10036514/26/20188
10036514/28/20188
5059354/23/20188
5059354/24/20188

 

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

 

 

AssignmentIDHours
aaaa11118
aaaa11128
aaaa11118
aaaa11148
aaaa11158
aaaa11168
aaaa11178
aaaa11188
aaaa11198
aaaa11208
aaaa11218
aaaa11228
aaaa11238
aaaa11248
aaaa11258
aaaa11268
aaaa11278
aaaa11288
aaaa11298
aaaa11308
aaaa11318
aaaa11328
aaaa11338
aaaa11348
aaaa11358
aaaa11368
aaaa11378
aaaa11388
aaaa11398
aaaa11408

 

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 3
v-yuezhe-msft
Employee
Employee

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.