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:

1. When the person clocked in early for a shift the next day.
2. When the clock time was rounded up to the next day.

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)

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.

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;
Code language: PHP (php)

1. 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.