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

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

Answer 3

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

Answer 4

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.

Answer 5

Hi @J Like MIB

How about like this，you concat all the Bonus Amount by the same employee，week and Hours worked.

DROP TABLE TABLETEST  
CREATE TABLE TABLETEST  
([Employee] VARCHAR(20),  
[Week] int,  
[Hours worked] int,  
[Bonus Amount] int)  
  
insert into TABLETEST  
select 'Employee 1',1,40,200  
union all  
select 'Employee 1',1,40,500  
union all  
select 'Employee 1',1,40,600  
union all  
select 'Employee 1',2,39,300  
union all  
select 'Employee 1',2,39,200  
union all  
select 'Employee 2',1,35,2000  
union all  
select 'Employee 2',1,35,200  
union all  
select 'Employee 2',2,10,500  
union all  
select 'Employee 2',2,10,300  
  
  
  
select [Employee],[Week],[Hours worked],[Bounus amout]=STRING_AGG(CONCAT('$',RTRIM(cast([Bonus Amount] as varchar(10)))),','),AVERAGE = CAST( sum([Bonus Amount])*1.00/[Hours worked] as decimal(5,2)) from TABLETEST GROUP BY [Employee],[Week],[Hours worked]  

Here is the result：

Best Regards，
Isabella