A client has both UKG Pro and UKG Kronos Dimensions. They want to see punch back up for each pay check.
The challenge is figuring out the apply date. Which is clearly on the screen but not to be found in the Data Hub Database. Here is what we did to figure out this key piece of information.
What is the apply date
The apply date tells you when a given span of hours will be paid.
Most of the time, this is the same as the date the person punched in.
In that case, the partitionDate in the vTimeCardWorkShiftSpan table will actually tie to the vTimeCardTotal table and then to the pay check.
There are two other situation you need to conser.
I’ve found two possible situations where you need a different date:
- When the person clocked in early for a shift the next day.
- When the clock time was rounded up to the next day.
Early start on a scheduled shift –
If a person is scheduled for a shift starting at midnight but clocks in early, the apply date will be the next day.
Here’s an example.
The person clocked in at 11:56 PM even though the shift started as 12:00 AM
And the apply date is 12/2.
To find this apply date, we need to use the vTimecardWorkShiftScheduledShift view which links the vTimeCardWorkShiftSpan view with the vScheduledShift view (see the code below)
Early start just before midnight when not scheduled
Sometimes, a person works even though they were not scheduled. If they start just before midnight, a rounding rule will push the apply date to the next day.
Here we can see in the time card that the person worked but was not scheduled:
If we choose Analyze:
What’s strange here is that the system moved the date forward even though the rule was to round to the nearest 15 minutes. Which just shows that you need to check how the system rounded and not try to apply your own rounding logic.
This appears to be a very similar case to the previous one. However, the exception is stored in the vTimeCardException view.
The Final Query
Here’s the final query which can show all times when the apply date will not be the partitionDate.
Code language: PHP (php)
WITH ExceptionCTE AS (SELECT exc.workedShiftSpanId, MAX(exc.eventDate) AS eventdate FROM `nyfoundling_detail.vTimecardException` exc GROUP BY exc.workedShiftSpanId), shiftDatesCTE AS (SELECT wsss.workedShiftId, MIN(ss.partitionDate) AS partitionDate FROM `nyfoundling_detail.vTimecardWorkShiftScheduledShift` wsss JOIN `nyfoundling_detail.vScheduleShift` ss ON wsss.shiftId = ss.shiftId GROUP BY wsss.workedShiftId) SELECT twss.personId, ppl.lastName, ppl.firstName, ppl.personNumber, twss.partitionDate, COALESCE(exc.eventdate, sd.partitionDate, twss.partitionDate) AS applyDate, twss.workedShiftSpanId, twss.startDateTime AS TimeCardWorkShiftSpan_StartDateTime, exc.eventdate AS Exception_EventDate, sd.partitionDate AS TimecardWorkShiftScheduledShift_PartitionDate FROM `nyfoundling_detail.vTimecardWorkShiftSpan` twss JOIN `nyfoundling_detail.vPeople` ppl ON twss.personId = ppl.personId LEFT OUTER JOIN ExceptionCTE exc ON exc.workedShiftSpanId = twss.workedShiftSpanId LEFT OUTER JOIN shiftDatesCTE sd ON twss.workedShiftId = sd.workedShiftId WHERE COALESCE(exc.eventdate, sd.partitionDate, twss.partitionDate) <> twss.partitionDate ORDER BY twss.partitionDate, ppl.personNumber;
Notes on the Code
- Both the vTimeCardException vand vTimecardWorkShiftScheduledShift views can have multiple records that match the vTimeCardWorkShiftSpan. This is why I have the individual common table expressions to avoid creating excess records.
- We generally run this query and store the result for use in other queries. This improves performance and makes other code easier to deal with.
I hope this helps you with your Data Hub Reporting. And that one day, this will not be necessary.
Have you found a better solution? Or do you have questions? Please comment below or contact us.