Challenge #494: Pay Period End Dates

[Kenda]

Not sure how many work hours @mkeiffer is logging this week from HI 😉

[mkeiffer]

@Kenda Still have two more days in Hawaii, but you are quite correct. Not doing too much work this week. 

[mmontgomery]

C494.

Spoiler

Found a way to use TS Filler!

[abacon]

Another fun one!

Spoiler

[JAKupchik]

Here is my solution.  Thanks!  JAK

 

[olga_strubbe]

Thank you, Michael, @mkeiffer

This was a fun challenge that could be solved in multiple of ways.  Here is one of  my solutions attached. 

The other solution would be to append the Period End Date to the original input and fillte records where OUT DATE <= Period End Date.  Then sort by Period End Date acsending for each of the records and output the first record for each of the 26 input rows. 

Spoiler

[pilsworth-bulien-com]

Fun challenge, always nice to get a quick win! 

Spoiler

If larger data were involved, I may have tried to do some different logic with a generate rows and join, instead of append, but for this sample size, I didn't feel the need. 

[TurboToad]

Started building an iterative macro but then went with a simpler solution. 😀

[aiahwieder]

Sneaky . . .

Spoiler

Don't let the pay periods overlap!

[SteveY02]

Solved. To my knowlage, there are two method.

Method 1: Using Cross join, following above steps:

1. Adding a constant join key to both table(Check Log and Pay Period). This allow a cross join to combine everything records in each table.

2. Using Filtter and with certain condition(out_date <= period_end_date) to keep only the valid data.

3. Using Summarize Tool to find the earliest  period end date for each record.

Method 2: Using Append Fields Tool.
Similar solution as Method 1, Only diference is using append fields tool to attach all periods end date to each records in check log table. The following steps are the same.