question

Sijuwades avatar image
Sijuwades asked

2 case statement

The human resource team needs to generate a report of all employees and if they are salaried or not On the report you need to display the following: If employee is salaried , display 'Exempt' else display 'Non-Exempt' . Show column name to read 'Exemption Status' HINT : [HumanResources].[Employee].So you successfully built the exemption report and now they want more data on the report :) HR needs you to add the total sum of PTO hours that each employee has (vacation hours and SickLeave Hours) On the report you need to display the following: If employee's PTO hours is greater than 40 hours , display 'Excess Payout' If employee's PTO hours is greater = 40 hours , display 'Regular Payout' else display 'No payout' . Show column name to read 'Payout Status' HINT : [HumanResources].[Employee]

case-statement
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sijuwades avatar image
Sijuwades answered

SELECT BusinessEntityID, SalariedFlag ,CASE WHEN SalariedFlag = 1 THEN 'Exempt' ELSE 'Non Exempt' END AS 'Exemption Status' FROM HumanResources.Employee

--this is the draft answer to the 1st case statement

--nesting the 2 cases together, I did:

SELECT BusinessEntityID, SalariedFlag ,CASE WHEN SalariedFlag = 1 THEN 'Exempt' ELSE 'Non Exempt' END AS 'Exemption Status' ,SUM(VacationHours + SickLeaveHours) AS 'PTO Hours' ,CASE WHEN COUNT('SUM(VacationHours + SickLeaveHours)') > 40 THEN 'Excess Payout' WHEN COUNT('SUM(VacationHours + SickLeaveHours)') = 40 THEN 'Regular Payout' ELSE 'No Payout' END AS 'Payout Status' FROM HumanResources.Employee GROUP BY BusinessEntityID, SalariedFlag

but I am not getting the resulted interpreted as desired!

Any suggestion?

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

but I am not getting the resulted interpreted as desired!

Do you mean on first query or the second query?

I don't think that the COUNT() is needed on the second one. The reason being is you're correctly adding up all the hours with SUM() to get the value you need to compare against. But then you go on to do a COUNT() which I figure in this case will just produce a 1.

0 Likes 0 ·
lokeshlehkara avatar image
lokeshlehkara answered

@WRBI is right. Count is not required.

SELECT BusinessEntityID, SalariedFlag ,CASE WHEN SalariedFlag = 1 THEN 'Exempt' ELSE 'Non Exempt' END AS 'Exemption Status' ,CASE WHEN SUM(VacationHours + SickLeaveHours) > 40 THEN 'Excess Payout' WHEN SUM(VacationHours + SickLeaveHours) = 40 THEN 'Regular Payout' ELSE 'No Payout' END AS 'Payout Status' FROM HumanResources.Employee GROUP BY BusinessEntityID, SalariedFlag

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.