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

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

Answer 3

It's my fault for not stating the MSSQL version. Our MSSQL version is still 2012 and IsabellaZhang's solution is only available for MSSQL2017.

Any other ideas for MSSQL2012?

Thank you all for viewing and replying.

Answer 4

For SQL 2012 this would work (based on Isabella's query):

SELECT
    [Employee]
    , [Week]
    , [Hours worked]
    , [Bonus amount] = STUFF((
                                 SELECT
                                     ',' + CONCAT('$', RTRIM(CAST([Bonus Amount] AS VARCHAR(10))))
                                 FROM
                                     dbo.TABLETEST T
                                 WHERE
                                     T.Employee = T2.Employee
                                     AND T.[Week] = T2.[Week]
                                     AND T.[Hours worked] = T2.[Hours worked]
                                 ORDER BY
                                     T.[Week]
                                 FOR XML PATH('')
                             )
                             , 1
                             , 1
                             , ''
                            )
    --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 T2
GROUP BY
    [Employee]
    , [Week]
    , [Hours worked];

Answer 5

Thank you all - that was all very helpful.