Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
what are the number of days used for Bi-Weekly and what are the number of days for semi-monthly?
Thanks Sunny -
14 Days for Bi-Weekly
15 Days for Semi monthly
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
];
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.
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;