# Help with math functions (calculating) !!!

Discussion in 'General Developer Questions' started by pharoah35, Jun 20, 2003.

1. ### pharoah35New Member

I have a SQL 2000 Database with a table which contain the following fields and desperately need Help creating the SQL mathematic function.

EM_NAME (Employee Name)
DOH (Date of Hire)
MGP(Monthly Gross Profit)
MBS(Employee#%92s Monthly Base Salary)
COMM_%(Commission Percentage)
COMM_PD(Commission Paid)

I need to create the following calculation.

If Today's Date ( subtracted ) from the Hire Date is ( > greater than ) than 1 Year . And the Employee generated a total profit ( = equal to or > greater than ) 2.5 times their monthly salary. Then pay the employee the sum of their monthly salary ( x times ) their commission ( % percentage ). ELSE Pat the Employee commission...

Any help would be greatly appreciated.
2. ### ChappyNew Member

try something like..

select e_Id, "Pay" =
CASE
WHEN (DATEDIFF(yy, hiredate, GETDATE()) > 1 and (MGP > 2.5*MBS))
THEN (MBS+(MGP * (COMM_PERC/100)))
ELSE MBS
END
FROM Employees

Also, im not sure i understood what you wanted to do, and so you may need to customise the logic.. but i think it was probably the query syntax and not the logic you were struggling with
3. ### pharoah35New Member

Chappy

Thank you so much for your help.
I will try to explain more clearly..

I have a SQL 2000 Database with a table which contain the following fields and desperately need Help creating the SQL mathematic function.

EM_NAME (Employee Name)
DOH (Date of Hire)
MGP(Monthly Gross Profit)
MBS(Employee#%92s Monthly Base Salary)
COMM_%(Commission Percentage)
COMM_PD(Commission Paid)

I need to create the following calculation.

If Today's Date is 1 year from the Date of Hire. And the Employee generated
a total profit equal to or greater than 2.5 times their monthly salary.
Then pay the employee the sum of their monthly salary x times their commission ( % percentage ). If not then pay the Employee \$0.00 commission...

Any help would be greatly appreciated.
4. ### gaurav_bindlishNew Member

Create Function DBO.CalCommission(@DOH Datetime, @MGP int, @MBS int, @Comm_% int)
returns int
as
begin
Declare @Commission int
Select @Commission = 1
if(Datediff(YY, @DOH, GetDate()) > 1) and (@MGP > 2.5*@MBS)
Begin
Select @Commission = @COMM_% / 100
End
Return (@Commission)
End

Use this function liek this-

Select EM_Name, MBS*DBO.CalCommission(@DOH, @MGP, @MBS, @Comm_%) AS SALARY
FROM <TableName>
If you don't want to use function, use chappy's solution.

HTH.

Gaurav
5. ### ChappyNew Member

Ok pharaoh, I understand what you want now.

But Im not sure why my query isnt the solution youre looking for ? My query lists all employees, along with their salary according to your calculation. Were you wanting a list of each employyes comission like guarav shows?
Or are you updating a table perhaps?
6. ### bambolaNew Member

Almost.
Almost because (DATEDIFF(yy, hiredate, GETDATE()) will not always return the correct year difference. I guess it's a bug...

Check this:

DECLARE @date datetime
SELECT @date = '2002-07-10'

SELECT DATEDIFF(YY, @date, GetDate())
-- returns 1

Do it with months
SELECT (DATEDIFF(month, @date, getdate()) / 12)
-- returns 0 as it should

Bambola.
7. ### gaurav_bindlishNew Member

Thanks Bambola for the correction. <br /><br />Chappy, I guess we have showed two ways of doing the same thing. Nice place to develop lateral thinking... [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Gaurav
8. ### ChappyNew Member

Good point bambola. I guess DATEDIFF just does the equivalent of a subtraction of the DATEPART on whatever you specify. Clearly a day in 2002, and a day in 2003 doesnt mean there is a year between them. I wouldnt say its a bug as such, probably just an oversight on my behalf.<br /><br />Guarav, yes, indeed <img src='/community/emoticons/emotion-1.gif' alt='' />
9. ### bambolaNew Member

Someone dared to suggest I'm older than I really am! [}<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />SELECT dbo.FN_DateDiff('19xx-09-24', GetDate())<br />SELECT DATEDIFF(YY, '19xx-09-24', GetDate()) <br /><br />So I had to come up with this:<pre><br />CREATE FUNCTION FN_DateDiff(@date1 datetime, @date2 datetime)<br />RETURNS int<br />AS<br />BEGIN<br /> RETURN (DATEDIFF(month, @date1, @date2) / 12)<br />END</pre><br />And I don't want to hear nothing about the xx!!! [<img src='/community/emoticons/emotion-2.gif' alt='' />] [8D]<br /><br />Bambola.<br /><br />
10. ### pharoah35New Member

Thank you all very much for your help
pharoah35
11. ### vbkenyaNew Member

An almost similar scenario (Wages and commisions) appears in BOL under the explanation for the COALESCE clause.

Nathan H.O.
12. ### ChappyNew Member

Ah yes, I knew id seen something like this before.
A similar scenario is also used on some of the MCDBA exams if I recall correctly