Forum Discussion
MERGE INTO statement increasing previously captured "snapshot" data
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