Creating Dynamic Pay-Dates

[ofalana1674]

Hi All -

I have a table that holds original paydates for a number of employees; all these dates are in the past, with some being over two years ago. The table looks like this -

EID       PayDate_Orig          PayCycle              

111        02/06/2015               Weekly

222       06/01/2016               Bi-Weekly

333       09/05/2017               Semi-monthly

444       01/08/2018               Monthly

I want to determine the future "NextPayDate" (after today) for each employee while also being able to determine old pay dates between the original pay date and today. I hope this makes sense.

I appreciate your help and time.

Thanks!

[sunny_talwar]

what are the number of days used for Bi-Weekly and what are the number of days for semi-monthly?

[ofalana1674]

Thanks Sunny -

14 Days for Bi-Weekly

15 Days for Semi monthly

[sunny_talwar]

Check this

Table:

LOAD *,

Date(If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num))) as PayDates

While If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num)) <= MonthStart(Today(), 2);

LOAD * INLINE [

    EID, PayDate_Orig, PayCycle, Num, Type

    111, 02/06/2015, Weekly, 7, Days

    222, 06/01/2016, Bi-Weekly, 14, Days

    333, 09/05/2017, Semi-monthly, 15, Days

    444, 01/08/2018, Monthly, 1, Months

];

[ofalana1674]

Thanks Sunny,

What if I only want the next pay date? Just a single date for each employee, so that EID 111 shows only 8/3/2018 as the PayDate.

[sunny_talwar · Accepted solution]

This

Table:

LOAD *

Where PayDates > Today();

LOAD *,

Date(If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num))) as PayDates

While If(Type = 'Days', PayDate_Orig + (IterNo()-1)*Num, AddMonths(PayDate_Orig, (IterNo()-1)*Num)) <= MonthStart(Today(), 2);

LOAD * INLINE [

    EID, PayDate_Orig, PayCycle, Num, Type

    111, 02/06/2015, Weekly, 7, Days

    222, 06/01/2016, Bi-Weekly, 14, Days

    333, 09/05/2017, Semi-monthly, 15, Days

    444, 01/08/2018, Monthly, 1, Months

];

Right Join (Table)

LOAD EID,

Min(PayDates) as PayDates

Resident Table

Group By EID;