Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Employee status on first and last day of a pay period RE: Employee status on first and last day of a pay period

  • March 26, 2015 at 9:11 pm

    #1786734

    Dave-3000 (3/26/2015)

    I am having a problem getting started on this problem, and I'm hoping someone has solved something similar and can guide me.

    I have a requirement in a payroll system to show the status of an employee on the first and last day of each pay period. The pay periods are pretty simple, 24 per year starting on the 1st and 16th of each month. The status effective date, however, can occur on any day, not just the first or last day of the pay period. Here's my DDL.

    I have some questions, please.

    1. What do you want to show on pay periods that are before the first entry for a given employee. For example, if an employee was hired on 05 May 2014 and your report is showing pay periods from 01 April 2014 thru 30 Jun 2014, what should the output be for that employee?

    2. What do you want to show on pay periods that are after an employee has been terminated. For example, if a full time employee was terminated on 05 May 2014 and your report is showing pay periods from 01 April 2014 thru 30 Jun 2014, what should the output be for that employee?

    --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)