Use a flag, or calculate, to retrieve counts

  • 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?

  • 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

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

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

  • 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".

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

  • This old article might be useful to you:

    https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/state-transition-constraints/

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • 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".

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ScottPletcher wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thank you.  An interesting article indeed.

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

  • edwardwill wrote:

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply