StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I'm not following the schema very well (it looks like each leave_type would have a carry forward? There's no user on the event* tables?) but you should be able to dynamically derive the balance at any point in time - including across years. </p> <p>AAMOF, normalization rules would require you to be able to <em>derive</em> the balance. If you then chose to <em>denormalize</em> for performance is up to you, but the design should support the calculated query. Given that, then calculating the year end carryforward is a single set based query.</p> <p>Edit: I had to change the schema a bit to accommodate this, and I chose to normalize to make the logic easier - but you can insert denormalization along the way for performance if you need to:</p> <p>First the tables that are important for this scenario...hopefully my pseudo-syntax will make sense:</p> <pre><code>User { User_Id (PK) } // Year may be a tricky business logic issue here...Do you charge the Start or End year // if the event crosses a year boundary? Or do you just do 2 different events? // You want year in this table, though, so you can do a FK reference to Leave_Allocation // Some RDBMS will let you do a FK from a View, though, so you could do that Event { Event_Id (PK), User_Id, Leave_Type_Id, Year, DtStart, DtEnd, ... // Ensure that events are charged to leave the user has FK (User_Id, Leave_Type_Id, Year)->Leave_Allocation(User_Id, Leave_Type_Id, Year) } Leave_Type { Leave_Type_Id, Year, Max_Carry_Forward // Max_Carry_Forward would probably change per year PK (Leave_Type_Id, Year) } // Starting balance for each leave_type and user, per year // Not sure the name makes the most sense - I think of Allocated as used leave, // so I'd probably call this Leave_Starting_Balance or something Leave_Allocation { Leave_Type_Id (FK->Leave_Type.Leave_Type_Id), User_Id (FK->User.User_Id), Year, Total_Days PK (Leave_Type_Id, User_Id, Year) // Ensure that leave_type is defined for this year FK (Leave_Type_Id, Year)->Leave_Type(Leave_Type_Id, Year) } </code></pre> <p>And then, the views (which is where you may want to apply some denormalization):</p> <pre><code>/* Just sum up the Total_Days for an event to make some other calcs easier */ CREATE VIEW Event_Leave AS SELECT Event_Id, User_Id, Leave_Type_Id, DATEDIFF(d, DtEnd, DtStart) as Total_Days, Year FROM Event /* Subtract sum of allocated leave (Event_Leave.Total_Days) from starting balance (Leave_Allocation) */ /* to get the current unused balance of leave */ CREATE VIEW Leave_Current_Balance AS SELECT Leave_Allocation.User_Id, Leave_Allocation.Leave_Type_Id, Leave_Allocation.Year, Leave_Allocation.Total_Days - SUM(Event_Leave.Total_Days) as Leave_Balance FROM Leave_Allocation LEFT OUTER JOIN Event_Leave ON Leave_Allocation.User_Id = Event_Leave.User_Id AND Leave_Allocation.Leave_Type_Id = Event_Leave.Leave_Type_Id AND Leave_Allocation.Year = Event_Leave.Year GROUP BY Leave_Allocation.User_Id, Leave_Allocation.Leave_Type_Id, Leave_Allocation.Year, Leave_Allocation.Total_Days </code></pre> <p>Now, our Leave CarryForward query is just the minimum of current balance or maximum carryforward as of midnight on 1/1.</p> <pre><code> SELECT User_Id, Leave_Type_Id, Year, /* This is T-SQL syntax...your RDBMS may be different, but should be able to do the same thing */ /* If not, you'd do a UNION ALL to Max_Carry_Forward and select MIN(BalanceOrMax) */ CASE WHEN Leave_Balance < Max_Carry_Forward THEN Leave_Balance ELSE Max_Carry_Forward END as Leave_Carry_Forward FROM Leave_Current_Balance JOIN Leave_Type ON Leave_Current_Balance.Leave_Type_Id = Leave_Type.Leave_Type_Id /* This assumes max_carry_forward is how much you can carry_forward into the next year */ /* eg,, a max_carry_forward of 300 hours for year 2008, means I can carry_forward up to 300 */ /* hours into 2009. Otherwise, you'd join on Leave_Current_Balance.Year + 1 if it's how much */ /* I can carry forward into *this* year. */ AND Leave_Current_Balance.Year = Leave_Type.Year </code></pre> <p>So, at the end of the year, you'd insert the CarryForward balances back into LeaveAllocation with the new year.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload