Average Pay by employee

Question

question

JLikeMIB-6608 asked Apr 26, '22 JLikeMIB-6608 answered Apr 29, '22

Average Pay by employee

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-transact-sql sql-server-reporting-services

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog · Apr 26 at 09:42 PM

Is it possible to filter / not display the 2nd Hours Worked if the same Employee number and week number?

This is something you should handle in the presentation layer, that is SSRS. You should never to this in a query directly.

No, I can't tell how to do it, because I don't know SSRS. But I would be very surprised is SSRS, if SSRS does not have this basic feature for reporting. I recall that many years ago when I worked in a project where we used Cobol (yes!) as the reporting tool, and we had this feature.

I see that you have tagged the query sql-reporting-services. Hopefully, someone who knows SSRS will chime in.

0 ·

Answer 1 · 2022-04-27T15:58:51.95Z

GuoxiongYuan-7218 answered Apr 27, '22 GuoxiongYuan-7218 edited Apr 27, '22

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:

image.png (5.9 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-04-26T17:12:30.117Z

NaomiNNN answered Apr 26, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-04-26T18:16:49.747Z

JLikeMIB-6608 answered Apr 26, '22 NaomiNNN commented Apr 26, '22

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN · Apr 26 at 06:30 PM

You're in the right place.

0 ·

Answer 4 · 2022-04-26T18:21:28.43Z

Viorel-1 answered Apr 26, '22 Viorel-1 commented Apr 27, '22

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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JLikeMIB-6608 · Apr 27 at 03:34 PM

Please clarify what do you mean by "no column for ordering the rows."?
You can sort by columns Employee ID and Week like you did - order by Employee, Week, i

Is that not it?

0 ·

Answer 5 · 2022-04-27T01:50:37.123Z

IsabellaZhangMSFT-7746 answered Apr 27, '22 JLikeMIB-6608 commented Apr 27, '22

Hi @JLikeMIB-6608

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

image.png (9.2 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JLikeMIB-6608 · Apr 27 at 03:37 PM

This is very helpful - unfortunately, our MSSQL is still version 2012. And STRING_AGG is not available until 2017.
Thank you very much for the suggestions though.

0 ·

Answer 6 · 2022-04-27T15:35:31.28Z

JLikeMIB-6608 answered Apr 27, '22 JLikeMIB-6608 edited Apr 27, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 7 · 2022-04-27T15:49:43.463Z

NaomiNNN answered Apr 27, '22

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];

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 8 · 2022-04-27T21:03:18.23Z

ErlandSommarskog answered Apr 27, '22

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 9 · 2022-04-29T16:03:55.987Z

JLikeMIB-6608 answered Apr 29, '22

Thank you all - that was all very helpful.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question