Average Pay by employee

Question

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

Answer 1

Try this:

DECLARE @Table TABLE (  
	[Employee] varchar(20),  
	[Week] int,  
	[Hours_Worked] int,  
	[Bonus_Amount] money  
);  
  
INSERT INTO @Table ([Employee], [Week], [Hours_Worked], [Bonus_Amount])  
VALUES   
('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);  
  
SELECT   
	p.[Employee],   
	[Week],   
	[Hours_Worked],  
	STUFF(  
		(  
			SELECT ',' + '$' + CAST([Bonus_Amount] AS varchar(10))   
			FROM @Table   
			WHERE [Employee] = p.[Employee] AND [Week] = p.[Week] AND [Hours_Worked] = p.[Hours_Worked]  
            FOR XML PATH(''), TYPE  
		).value('.', 'NVARCHAR(MAX)'),  
		1,  
		1,  
		''  
	) AS [Bonus_Amount],  
	CAST(SUM([Bonus_Amount]) / [Hours_Worked] AS decimal(5, 2)) AS [Average]  
FROM @Table AS p  
GROUP BY p.[Employee], [Week], [Hours_Worked];  
GO  

Output:

Answer 2

Working from Isabella's script, here is a more normal query that returns the data in a relational format:

SELECT Employee, Week, "Hours worked", "Bonus Amount",
       Average = 1.0 * SUM("Bonus Amount") OVER(PARTITION BY Employee, Week) /
                 AVG("Hours worked") OVER(PARTITION BY Employee, Week)
FROM  TABLETEST

Answer 3

I think you're talking presentation layer, not the query. What are you using for presenting the data?

Answer 4

Thank you Naomi.
Sql Server Reporting Server (SSRS). I'm in the wrong community then?

Answer 5

I think that the request is problematic because there is no column for ordering the rows. Maybe your real data contains an appropriate column.

If you prefer T-SQL, then try something like this:

;
with T1 as
(
    select *,
        row_number() over (partition by Employee, [Week] order by @@spid) as i,
        sum([Bonus Amount]) over (partition by Employee, [Week]) as s
    from MyTable
),
T2 as
(
    select *,
        row_number() over (partition by Employee, [Week] order by i desc) as j
    from T1
)
select Employee, [Week],   
    case i when 1 then cast([Hours worked] as varchar(max)) else '' end as [Hours worked],
    [Bonus Amount],
    case j when 1 then cast(s / [Hours Worked] as varchar(max)) else '' end as [Average]
from T2
order by Employee, Week, i

Also check if you can do this in SSRS without writing the query.