I am hoping this is a simple and common issue that I lack the skill for and the language to search for a solution…
This takes place in the nonprofit/human service sector. An employee’s shift must include 3 events in 3 separate systems:
- The clocking in and out of a state-sponsored system (sometimes initiated by the employee, sometimes a client/family).
- Clocking in and out of the companies payroll system.
- Documenting the services provided during the time periods above.
Each process is its own Fact table (The payroll system Fact table is not in the model yet). I need to be able to compare the start times of the systems and flag events that occur greater than 8 minutes apart. I thought this would be a simple datediff. I want to use this to replace this current table where the manager has to eyeball the difference. What am I missing?
Below is a screenshot of the data model.