Post reply

Bringing forward compound salaries by periodic salary increases in forecastscycle

  • March 9, 2012 at 4:18 am

    #265682

    Hi,

    I have an Employee_Salary table with Columns

    Rowid,Employee,Salary,Salary_Increase_Rate,Period,Dept

    There are 2000 employees each with 24 sequential periods. Their Yearly Base Salary is divided by 12 and allocated to each period. Forecasters then put a rate increase against a period to signal a change of rate, which needs to result in the Base salary being compounded with the increase. If the SALARY_RATE_INCREASE is null, the value remains unchanged, else its updated and compounded again.

    i:e

    Employee Period Salary Salary_Rate_Increase Dept

    Bob 201306 12000 NULL SALES

    Bob 201307 12000 NULL SALES

    Bob 201308 12000 NULL SALES

    Bob 201309 12036 0.003 SALES

    Bob 201310 12036 NULL SALES

    Bob 201311 12036 NULL SALES

    Bob 201312 12096 0.005 SALES

    Bob 201401 12096 NULL SALES

    Bob 201402 12096 NULL SALES

    Sally 201306 12000 NULL SALES

    Sally 201307 12000 NULL SALES

    Sally 201308 12000 NULL SALES

    Sally 201309 12036 0.003 SALES

    Sally 201310 12036 NULL SALES

    Sally 201311 12036 NULL SALES

    Sally 201312 12096 0.005 SALES

    Sally 201401 12096 NULL SALES

    Sally 201402 12096 NULL SALES

    My approach is to use a cursor loop to read each row until the first increase, and leave the Salaries unchanged if there is a NULL SALARY_RATE_INCREASE in the first Block - thats easy. Also, I would update the NULL SALARY_RATE_INCREASE to 0.000001, which lets the same bloc of code move past this in a second pass and attend to the first update, in Period 201309. (later I round(SALARY_RATE_INCREASE,4 )which returns 0)

    My issue now is how to bring the SALARY of 201310 forwards and deal with the NULL SALARY_INCREASE all over again - does this call for a nested cursor to deal with the first block -and outer cursors to deal with 3, 4 or n future increases? There are multiple records per employee, multiple employees per Department

    Thanks in advance

  • March 9, 2012 at 8:43 pm

    #1457435

    Your use of all caps for column names would indicate that you might be trying to do this in Oracle. Are you using SQL Server or Oracle for this? I ask because I only have an answer for SQL Server and I can guarantee you it won't work in Oracle or any other RDBMS other than SyBase, which uses the same "Rushmore" engine as SQL Server.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 9, 2012 at 8:46 pm

    #1457437

    Its Sql Server 2008 r2, cheers

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply