Use a flag, or calculate, to retrieve counts

Question

Use a flag, or calculate, to retrieve counts

edwardwill

SSCertifiable

Points: 5851
More actions
March 6, 2023 at 8:18 am

#4157179
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?

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

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

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

edwardwill SSCertifiable Points: 5851 More actions · Answer 2

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.

CreateIndexNonclustered SSChasing Mays Points: 648 More actions · Answer 3

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.

ScottPletcher SSC Guru Points: 101095 More actions · Answer 4

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

edwardwill SSCertifiable Points: 5851 More actions · Answer 5

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.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

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.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

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)

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

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)

edwardwill SSCertifiable Points: 5851 More actions · Answer 9

edwardwill

SSCertifiable

Points: 5851

March 15, 2023 at 8:05 am

#4160922

Thank you. An interesting article indeed.

edwardwill SSCertifiable Points: 5851 More actions · Answer 10

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.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 11

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)