July 4, 2019 at 4:55 pm
I have a query which select employees salary, I need to bring all the employees and positions where the salary is higher that the budget. However, my problem is that some positions have the max salary listed as hourly rate and some are annual amount. Therefore, I need to multiply the hourly rate by 2080 to get the annual salary.
Please help
Select emp_code, emp_name,emp_last_name, emp_max_pay, emp_salary
CASE
WHEN emp_max_pay = '##.##' THEN emp_max_pay * 2080
ELSE END
July 4, 2019 at 6:51 pm
Without seeing the data or having any idea of what things look like, what I would do is something like this for your case statement:
CASE
WHEN emp_max_pay < 10000
THEN emp_max_pay * 2080
WHEN emp_max_pay >= 10000
THEN emp_max_pay
END AS Actual_emp_max_pay
Mind you, my guess of 10,000 as being a "too high for hourly" emp_max_pay is really just a guess... that number may be too high or too low in some cases.
As for the "TSQL or SP", I would do it as a calculated column on the table or as a function.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 4, 2019 at 7:05 pm
I would not recommend putting an excel file on here. Not many people will nab that as there is inherit risk of downloading random files from the internet...
I would just post up some sample data in SQL format. Something along the lines of:
CREATE TABLE @sampleData (ID INT, Employee VARCHAR(255), emp_max_pay FLOAT, emp_salary FLOAT)
INSERT INTO @sampleData
VALUES
(1, 'test user', 10.00, 100000),
(2, 'test 2', 100000,100000)
...
and so on. Having us create the code to get sample data is going to slow down responses.
That being said, I think what I posted for your CASE should work; I would still do it as a calculated column or a function though rather than a stored procedure or straight TSQL. Even a VIEW would work.
But it does depend on what you are using it for. If it is for a report, you could do that calculation on the report side. If it is for an application, could do it on the application side. There are a lot of places you can do this.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 4, 2019 at 7:49 pm
That being said, I think what I posted for your CASE should work;
Your sample SQL assumes a maximum hourly rate of 10,000! Maybe I am in the wrong career 🙂
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
July 5, 2019 at 12:48 pm
Hi bmg002
Yes, it work thank you, I need to practice more function and store procedures as well.
July 5, 2019 at 1:02 pm
because now when I use the actual max in the where clause is not working I received the error "invalid column name"
where Actual_max_pay > emp_salary
July 5, 2019 at 3:46 pm
The WHERE clause runs before the COLUMN list, despite being coded later. Your WHERE clause needs to have the same calculation, or you need a CTE to do this ahead and materialize a column that you can use. If you are having issues, post the code. Don't assume we know what code you've written and can debug it.
Likely you need something like:
with cteCalc (something, actual_emp_max_pay)
as
( select something,
CASE
WHEN emp_max_pay < 10000
THEN emp_max_pay * 2080
WHEN emp_max_pay >= 10000
THEN emp_max_pay
END AS Actual_emp_max_pay
from somewhere
)
select *
from cteCacl
where actual_emp_max_pay > 5
July 5, 2019 at 4:21 pm
With the sample hourly rate being 10,000, I was just trying to make sure I picked a value that was substantially large enough to cover almost everything possible in the hourly range, but also be low enough that it shouldn't hit a yearly salary. Even doing 500 would probably cover all of the cases if you assume the emp_max_pay is always in USD, but 10,000 should also account for most other currencies, not just USD. For example, 60 USD is 6,513 Yen. I am sure it doesn't cover every possible currency using the 10,000 as the limit though.
Another way you could do the calculation for the CASE statement instead of hard-coding to a magic number of 10,000 would be to divide the emp_ann_sal by the emp_max_pay and if the value was higher than 3, then do the multiplication by 2080. Although, you would then miss anybody who makes 3 times their max pay or more, but those cases should be very small. Using the divide and compare method will give you slower calculations, but also more accurate results across currencies.
What I mean is for your CASE statement:
CASE
WHEN (emp_max_pay/ISNULL(NULLIF(emp_ann_sal,0),1) > 3)
THEN emp_max_pay * 2080
ELSE
emp_max_pay
END AS Actual_emp_max_pay
Although if anybody ever finds a job where they pay 10,000 USD per hour, I'll send you my resume!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
July 5, 2019 at 4:32 pm
> my problem is that some positions have the max salary listed as hourly rate and some are annual amount. Therefore, I need to multiply the hourly rate by 2080 to get an annual salary. <<
No, you need to correct your DDL. You had one column that held unrelated data elements, such as shoe size and somebody's IQ, would immediately see the problem. What you've done is got a column that holds one of two different rates. This is as dumb as having a measurement in either metric or US customary units. It just doesn't fundamentally work. It would've also been really nice he had bothered to follow netiquette and actually posted some DDL.
Please post DDL and follow ANSI/ISO standards when asking for help.
July 5, 2019 at 6:58 pm
I prefer to use CROSS APPLY(s) for such calcs.
Select emp_code, emp_name,emp_last_name, emp_max_pay, emp_yearly_salary
From dbo.table_name
Cross Apply (
Select case when emp_max_pay < 10000 then emp_max_pay * 2080 else emp_max_pay end as emp_yearly_salary
) As Alias1
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
July 5, 2019 at 9:50 pm
> my problem is that some positions have the max salary listed as hourly rate and some are annual amount. Therefore, I need to multiply the hourly rate by 2080 to get an annual salary. <<
No, you need to correct your DDL. You had one column that held unrelated data elements, such as shoe size and somebody's IQ, would immediately see the problem. What you've done is got a column that holds one of two different rates. This is as dumb as having a measurement in either metric or US customary units. It just doesn't fundamentally work. It would've also been really nice he had bothered to follow netiquette and actually posted some DDL.
Sometimes you just need to deal with the garbage data that others give you.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply