March 6, 2023 at 8:18 am
I'm working on a Holiday Leave system (for typically several hundred or more personnel). Currently, a person may request leave (in the future) and each Leave request can have one of a number of Status values:
Pending Approval
Approved
Declined
Cancelled
There is a report required to sum up these totals for an individual, broken down by status and Quarter. However, there is no Status corresponding to "Taken". This, I am told, is calculated by summing up Leave with a status of Approved which occurs in the past.
Instinctively I don't like this model - I would prefer to have an explicit status value of Taken. This could be written in a regular job (SQL Agent or Azure function) e.g.
UPDATE [Leave] SET Status = TAKEN_VALUE WHERE Status = APPROVED_VALUE AND LeaveDate <= CURRENT_TIMESTAMP
Does anyone agree? Or am I making a fuss about nothing?
March 6, 2023 at 12:59 pm
Imo it depends on whether or not Approved is exactly equivalent to Taken. Could a user be officially approved and yet not take the day? If there's an exact equivalency then an additional column would be wasteful imo
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
March 6, 2023 at 1:03 pm
The heuristic is that if the Status = Approved AND Date < Today then it is accepted as Taken.
I apologise if I didn't make myself clear: I'm not suggesting an additional column but I am wondering if the existing Status column could accept another value - Taken, or whether the current model (where Status = Approved AND Date < Today denotes Taken) is adequate and sufficient.
March 6, 2023 at 5:44 pm
Having worked with timecard and leave reporting in the past, if you are looking for better accuracy with that additional status, I would expect your results to be mixed at best. Even with strict organizational requirements on timely and accurate time recording, updates and corrections weeks and months were common for me and corrections that took place years after a time keeping record, were not unheard of.
That said, I am not a fan at all of ever inferring a property based on assumptions or conclusions about the outcomes of another unrelated property. In this case however, I don't think recording the taken status in the requests table is the correct place to record it, it should be in the timecard with a record for the leave pay type with a reference to the approval.
March 6, 2023 at 6:41 pm
The current approach seems ok to me, as long as people are rigorous about changing the status to "Declined" or "Cancelled" if the day can't be taken as scheduled.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
March 7, 2023 at 8:50 am
Thanks everyone. As I said in my original post, instinctively I felt that this was a "no-brainer" but I'm now content to proceed with the current model (not that I could easily change it, without raising a CR and all that hassle and then justify it to the POs etc.) The good thing is that the report simply looks at the data, so that a record with a status of APPROVED that is in the past will be counted as TAKEN. If, a day or so later, that record is marked as CANCELLED, it will NOT be counted as TAKEN.
March 14, 2023 at 8:43 pm
This old article might be useful to you:
Please post DDL and follow ANSI/ISO standards when asking for help.
March 14, 2023 at 9:29 pm
Imo it depends on whether or not Approved is exactly equivalent to Taken. Could a user be officially approved and yet not take the day? If there's an exact equivalency then an additional column would be wasteful imo
This is a powerful point. There have been many a time when I'm called in an emergency while on vacation. IMHO, "Approved in the past" is not equal to "Taken".
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2023 at 9:33 pm
The current approach seems ok to me, as long as people are rigorous about changing the status to "Declined" or "Cancelled" if the day can't be taken as scheduled.
That's what we ended up doing. That would satisfy the "Approved in the past" <> "Taken" in my book. It also lets people sort of calculate whose vacations are interrupted. I'd still rather see a "Recalled" status rather than relying on a "Cancelled" status because a "Cancelled" could mean it was "Cancelled" for any reason where "Recalled" would mean "Was on vacation time, which had to be cancelled due to work".
I'll also say that the temporal nature of what a status can be is well stated by Joe in his article. For example, you should not be able to have a "Recalled" status if the status was never "Approved". The same holds true for "Cancelled". If it were me, I'd like to see more than just the 2 reasons other than just "Recalled" and "Cancelled" but maybe that just me.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 15, 2023 at 8:05 am
Thank you. An interesting article indeed.
March 15, 2023 at 8:07 am
Steve Collins wrote:Imo it depends on whether or not Approved is exactly equivalent to Taken. Could a user be officially approved and yet not take the day? If there's an exact equivalency then an additional column would be wasteful imo
This is a powerful point. There have been many a time when I'm called in an emergency while on vacation. IMHO, "Approved in the past" is not equal to "Taken".
In this instance Approved in the past is exactly equivalent to Taken. If at some later point the member of staff states that they did not, in fact, take the Leave, then the status of the Leave record could be changed to Cancelled.
March 15, 2023 at 9:44 pm
Jeff Moden wrote:Steve Collins wrote:Imo it depends on whether or not Approved is exactly equivalent to Taken. Could a user be officially approved and yet not take the day? If there's an exact equivalency then an additional column would be wasteful imo
This is a powerful point. There have been many a time when I'm called in an emergency while on vacation. IMHO, "Approved in the past" is not equal to "Taken".
In this instance Approved in the past is exactly equivalent to Taken. If at some later point the member of staff states that they did not, in fact, take the Leave, then the status of the Leave record could be changed to Cancelled.
Then that should work just fine. Thank you for the feedback.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply