question

JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 asked JLikeMIB-6608 answered

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-sqlsql-server-reporting-services
· 1
5 |1600 characters needed characters left characters exceeded

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

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 Votes 0 ·
GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered GuoxiongYuan-7218 edited

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




image.png (5.9 KiB)
5 |1600 characters needed characters left characters exceeded

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

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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

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

JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 answered NaomiNNN commented

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

· 1
5 |1600 characters needed characters left characters exceeded

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

You're in the right place.

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 commented

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

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

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 Votes 0 ·
Isabellaz-1451 avatar image
0 Votes"
Isabellaz-1451 answered JLikeMIB-6608 commented

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:

196781-image.png


Best Regards,
Isabella


image.png (9.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

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

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 Votes 0 ·
JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 answered JLikeMIB-6608 edited

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

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

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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

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

ErlandSommarskog avatar image
2 Votes"
ErlandSommarskog answered

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

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

JLikeMIB-6608 avatar image
0 Votes"
JLikeMIB-6608 answered

Thank you all - that was all very helpful.

5 |1600 characters needed characters left characters exceeded

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