SQL Server Performance

Help with math functions (calculating) !!!

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

  1. pharoah35 New 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. Chappy New 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

    Please note that if you had posted scripts to create your tables it is easier for people to help you.

    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. pharoah35 New 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_bindlish New 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. Chappy New 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. bambola New 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_bindlish New 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. Chappy New 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. bambola New 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=':D' />] [8D]<br /><br />Bambola.<br /><br />
  10. pharoah35 New Member

    Thank you all very much for your help
    pharoah35
  11. vbkenya New Member

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

    Nathan H.O.
  12. Chappy New 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

Share This Page