How can I get correct payperiod from date?

Question

I feel like this is math problem more than anything. My company has employees all over the country. Some parts of the company are on an "odd" pay cycle and some are on "even". I call the starting date of a given pay period a "payperiod". I need to do two things:

1) determine the payperiod in which a given date falls

//Something like this:
public static DateTime getPayPeriodStartDate(DateTime givenDate, string EvenOrOdd)
{ .. }

2) get a list of payperiods between two dates:

//Something like this:
public static List<DateTime> getPayPeriodsBetween(DateTime start, DateTime end, string EvenOrOdd)
{ .. }

I'm using a couple dates as fixed standards on which to base any future pay period dates. The fixed standard dates for even and odd are as follows:

• Even - 01/04/09
• Odd - 01/11/09

Each pay period starts on the sunday of the week and goes for two weeks. For instance, using the standard dates above, the first even pay period starts on 01/04/09 and ends on 01/17/09. The first odd pay period starts on 01/11/09 and ends on 01/24/09. As you can see, there is some overlap. We have thousands of employees so it's necessary to split them up a bit.

I have a solution that is based on week numbers but it's clunky and has to be "fixed" every new year. I'm wondering how you would handle this.

Answer 1

I had a need to do something similar and was able to do it very easily using LINQ. Simply build up a List for even and odd and then query between dates from the odd/even as necessary. Also, I recommend you move to an emum for parameters like EvenOrOdd where you have fixed values.

Answer 2

I had a similar problem a few months ago, and I ended up writing a quick script to create entries in a database for each pay period so I never had to actually do the math. This way, The system works the same speed, and doesn't have to do any slow iterations every time a period is requested.

That being said, you can always take the starting date, and add two weeks (or however long your periods are) over and over until you reach the dates you specify in the function call. This is a bit ugly, and the longer it sits in production, the slower it gets (since the dates are getting further and further apart).

Both ways are trivial to implement, it's just a matter of what kind of resources you have at hand to tackle the issue.

So, for number 1: Start with either 1/4/2009 or 1/11/2009 (depending on even/odd pay week) and add 2 weeks until the givenDate is less than the date you're testing + 2 weeks. That's the start of the period.

For number 2: Same thing, start at the date and add 2 weeks until you're within the date range. While you're there, add each item to a list. As soon as you're past the last date, break out of your loop and return your shiny new list.

If you used my method and went with a database to house all this info, it turns into 2 simple queries:

1)SELECT * FROM payperiods WHERE startdate<=givenDate ORDER BY startdate LIMIT 1

2) SELECT * FROM payperiods WHERE startdate>=givenDate AND enddate<=givenDate ORDER BY startdate

Answer 3

I haven't tested for many test cases, but I think this fits the bill:

public static DateTime getPayPeriodStartDate(DateTime givenDate, string EvenOrOdd)
{
    DateTime newYearsDay = new DateTime(DateTime.Today.Year, 1, 1);
    DateTime firstEvenMonday = newYearsDay.AddDays((8 - (int)newYearsDay.DayOfWeek) % 7);
    DateTime firstOddMonday = firstEvenMonday.AddDays(7);
    TimeSpan span = givenDate - (EvenOrOdd.Equals("Even") ? firstEvenMonday : firstOddMonday);
    int numberOfPayPeriodsPast = span.Days / 14;
    return (EvenOrOdd.Equals("Even") ? firstEvenMonday : firstOddMonday).AddDays(14 * numberOfPayPeriodsPast);
}

public static List<DateTime> getPayPeriodsBetween(DateTime start, DateTime end, string EvenOrOdd)
{
    DateTime currentPayPeriod = getPayPeriodStartDate(start, EvenOrOdd);
    if (currentPayPeriod < start) currentPayPeriod = currentPayPeriod.AddDays(14);
    List<DateTime> dtList = new List<DateTime>();
    while (currentPayPeriod <= end)
    {
        dtList.Add(currentPayPeriod);
        currentPayPeriod = currentPayPeriod.AddDays(14);
    }
    return dtList;
}

I am sure it can be improved.

Answer 4

Not fully optimized or tested, but this is what I came up with:

const int DaysInPeriod = 14;

static IEnumerable<DateTime> GetPayPeriodsInRange(DateTime start, DateTime end, bool isOdd)
{
    var epoch = isOdd ? new DateTime(2009, 11, 1) : new DateTime(2009, 4, 1);
    var periodsTilStart = Math.Floor(((start - epoch).TotalDays) / DaysInPeriod);

    var next = epoch.AddDays(periodsTilStart * DaysInPeriod);

    if (next < start) next = next.AddDays(DaysInPeriod);

    while (next <= end)
    {
        yield return next;
        next = next.AddDays(DaysInPeriod);
    }

    yield break;
}

static DateTime GetPayPeriodStartDate(DateTime givenDate, bool isOdd)
{
    var candidatePeriods = GetPayPeriodsInRange(givenDate.AddDays(-DaysInPeriod), givenDate.AddDays(DaysInPeriod), isOdd);
    var period = from p in candidatePeriods where (p <= givenDate) && (givenDate < p.AddDays(DaysInPeriod)) select p;
    return period.First();
}