How do I write a tsql that will filter certain column(s) / field(s) based on a column / field.
My current data for example:
Employee Week Hours worked Bonus Amount
Employee 1 1 40 $200
Employee 1 1 40 $500
Employee 1 1 40 $600
Employee 1 2 39 $300
Employee 1 2 39 $200
Employee 1 2 39 $800
Employee 2 1 35 $2000
Employee 2 1 35 $200
Employee 2 2 10 $500
Employee 2 2 10 $300
So, in the example, Employee 1 in Week 1 worked 40 hours, not 120 hours. Therefore, I'm trying to get the data to display and calculate average bonus based on 40 hours:
Employee Week Hours worked Bonus Amount Average
Employee 1 1 40 $200
Employee 1 1 $500
Employee 1 1 $600 32.50
Employee 1 2 39 $300
Employee 1 2 $200 12.82
Employee 2 1 35 $2000
Employee 2 1 $200 62.85
Employee 2 2 10 $500
Employee 2 2 $300 80.00
Is it possible to filter / not display the 2nd Hours Worked if the same Employee number and week number?
Any help is much appreciated. Thank you