Average Pay by employee

J Like MIB 71 Reputation points
2022-04-26T16:30:47.963+00:00

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

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,772 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2022-04-27T15:58:51.95+00:00

    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:

    197091-image.png

    0 comments No comments

8 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 100.1K Reputation points MVP
    2022-04-27T21:03:18.23+00:00

    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
    
    2 people found this answer helpful.
    0 comments No comments

  2. Naomi 7,361 Reputation points
    2022-04-26T17:12:30.117+00:00

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

    0 comments No comments

  3. J Like MIB 71 Reputation points
    2022-04-26T18:16:49.747+00:00

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


  4. Viorel 110.7K Reputation points
    2022-04-26T18:21:28.43+00:00

    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.