Forum Discussion
Solved
MERGE INTO statement increasing previously captured "snapshot" data
Hello, I need help understanding why my snapshot logic isn't working: I have a table that captures "snapshot" data for staff absences each day. On Friday 11th I did a simple select query which re...
This may not be a technical issue at all.
You need to consider how leave is administered, which would involve talking to departments like human resources and payroll, etc.
In my experience, it's quite common for unplanned leave (such as sick or compassionate leave, etc.) to be entered retrospectively.
I've also seen a good number of places enter leave batch-style, such as on a particular day of the week, or when their in-tray/inbox has enough pending request to make it worthwhile.
And then there's just plain old corrections - whether that's because someone entered the dates incorrectly in the first place, or perhaps the leave dates were adjusted at the person on leave's request.
The point is, there's a human behaviour issue at play here - it's not a real time technical transaction process.
Given the volume of rows you're pulling, that's a very low number of variations.
Your best course of actions is to pick a handful of the variations and go check with the custodians (HR, payroll, whoever it may be). Maybe there's a problem but I doubt it.
I'm more inclined to think your assumption that for a given historic date that the reported figure won't change is not realistic. Certainly, the further you go back in time the more accurate that assumption will probably be, but where you're searching against recent dates - as your example is, and by the time you factor in the length of a pay cycle (often fortnightly or monthly in my experience), you can expect to see variations.
Cheers,
Lain
This may not be a technical issue at all.
You need to consider how leave is administered, which would involve talking to departments like human resources and payroll, etc.
In my experience, it's quite common for unplanned leave (such as sick or compassionate leave, etc.) to be entered retrospectively.
I've also seen a good number of places enter leave batch-style, such as on a particular day of the week, or when their in-tray/inbox has enough pending request to make it worthwhile.
And then there's just plain old corrections - whether that's because someone entered the dates incorrectly in the first place, or perhaps the leave dates were adjusted at the person on leave's request.
The point is, there's a human behaviour issue at play here - it's not a real time technical transaction process.
Given the volume of rows you're pulling, that's a very low number of variations.
Your best course of actions is to pick a handful of the variations and go check with the custodians (HR, payroll, whoever it may be). Maybe there's a problem but I doubt it.
I'm more inclined to think your assumption that for a given historic date that the reported figure won't change is not realistic. Certainly, the further you go back in time the more accurate that assumption will probably be, but where you're searching against recent dates - as your example is, and by the time you factor in the length of a pay cycle (often fortnightly or monthly in my experience), you can expect to see variations.
Cheers,
Lain
- Hi Lian,
Thanks for the advice. I believe you're right. I have been advised to redevelop the ETL to load all live records and assign a snapshot date rather than an accumulation of changes.
Cheers,That sounds like good advice, as the merging approach can give misleading data.
Consider you have a person taking leave with these initial dates:
Start date End date 2023-08-17 2023-08-30 But then later on, they have to adjust the leave they applied for above to this:
Start date End date 2023-09-17 2023-09-30 The problem with the MERGE approach is that for this person, they now have two leave windows scheduled:
Start date End date 2023-08-17 2023-08-30 2023-09-17 2023-09-30 So, for this poor person, it looks like they're taking off twice as much time as they really are. It obviously also makes the organisational leave rollup inaccurate.
MERGE is great for things change logging/auditing but not for this kind of scenario, where - as you say - using the live/final records is the more trustworthy approach when measuring the amount of organisational leave being taken per period.
Cheers,
Lain
