Leave Balance Calculation

Understanding how leave balances are calculated in CHRIS.

Overview

Leave balance represents the number of days an employee can take off. The calculation considers:

• Entitlement: Days granted per year
• Used: Days already taken
• Carryover: Days from previous year
• Pending: Days in approved but future requests

Balance Formula

Available Balance = Entitlement + Carryover - Used - Pending

Where:

Example Calculation

Employee: John Doe

• Contract entitlement: 20 days/year
• Carryover from 2024: 3 days
• Leave taken in 2025: 8 days
• Approved future leave: 2 days

Available = 20 + 3 - 8 - 2 = 13 days

Entitlement Sources

Contract-Based Entitlement

Primary source from the contracts table:

SELECT yearly_leave_days
FROM contracts
WHERE employee_id = 'user-uuid'
AND (end_date IS NULL OR end_date >= CURRENT_DATE)
ORDER BY start_date DESC
LIMIT 1;

Profile Override

Can be overridden in the profiles table:

SELECT annual_entitlement_days
FROM profiles
WHERE id = 'user-uuid';

If annual_entitlement_days is set, it takes precedence over contract value.

Calculation Priority

1. Check profiles.annual_entitlement_days
2. If null, use contracts.yearly_leave_days
3. If no contract, default to 0

Carryover Days

What is Carryover?

Days not used in the previous year that can be used in the current year.

Carryover Limits

Common policies:

Carryover Storage

Stored in profiles.carryover_days:

UPDATE profiles
SET carryover_days = 5
WHERE id = 'user-uuid';

Annual Carryover Process

At year end:

1. Calculate unused days from current year
2. Apply carryover policy (limits, expiry)
3. Update carryover_days for new year
4. Reset for new entitlement period

Used Days Calculation

Counting Used Days

Sum of working days from approved requests in the past:

SELECT COALESCE(SUM(working_days), 0) as used_days
FROM leave_requests
WHERE user_id = 'user-uuid'
AND status = 'approved'
AND leave_type_id = 'annual-leave-type-uuid'
AND start_date <= CURRENT_DATE
AND EXTRACT(YEAR FROM start_date) = EXTRACT(YEAR FROM CURRENT_DATE);

Leave Type Separation

Each leave type has its own balance:

• Annual leave: 20 days entitlement
• Sick leave: Unlimited (or capped)
• Family leave: Separate allocation

Working Days Calculation

What Counts as a Working Day?

Working days exclude:

• Weekends: Saturday and Sunday
• Public holidays: From assigned holiday scheme

Calculation Example

Leave request: Monday Dec 22 - Friday Dec 26, 2025

Working days: 3 (not 5 calendar days)

Implementation

function calculateWorkingDays(
  startDate: Date,
  endDate: Date,
  holidays: Date[]
): number {
  let workingDays = 0;
  const current = new Date(startDate);

  while (current <= endDate) {
    const dayOfWeek = current.getDay();
    const isWeekend = dayOfWeek === 0 || dayOfWeek === 6;
    const isHoliday = holidays.some(h =>
      h.toDateString() === current.toDateString()
    );

    if (!isWeekend && !isHoliday) {
      workingDays++;
    }

    current.setDate(current.getDate() + 1);
  }

  return workingDays;
}

Balance by Leave Type

Tracking Different Types

Each leave type can have different rules:

Query by Type

SELECT
  lt.name_en as leave_type,
  COALESCE(SUM(lr.working_days), 0) as days_used
FROM leave_types lt
LEFT JOIN leave_requests lr ON lt.id = lr.leave_type_id
  AND lr.user_id = 'user-uuid'
  AND lr.status = 'approved'
  AND EXTRACT(YEAR FROM lr.start_date) = 2025
GROUP BY lt.id, lt.name_en;

Balance Display

Dashboard Widget

function LeaveBalanceCard() {
  const { data: balance } = useQuery({
    queryKey: ['leave-balance'],
    queryFn: fetchLeaveBalance
  });

  return (
    <Card>
      <CardHeader>Leave Balance</CardHeader>
      <CardContent>
        <div className="grid grid-cols-2 gap-4">
          <div>
            <Label>Annual Leave</Label>
            <Value>{balance.annual.available} / {balance.annual.total}</Value>
          </div>
          <div>
            <Label>Sick Leave</Label>
            <Value>{balance.sick.used} used</Value>
          </div>
        </div>
      </CardContent>
    </Card>
  );
}

Insufficient Balance Warning

When submitting a request that exceeds available balance:

if (requestedDays > availableBalance) {
  return (
    <Alert variant="warning">
      <AlertTitle>Insufficient Balance</AlertTitle>
      <AlertDescription>
        You are requesting {requestedDays} days but only have
        {availableBalance} available.
      </AlertDescription>
    </Alert>
  );
}

Pro-Rata Calculations

Mid-Year Starters

Employees starting mid-year get proportional entitlement:

Pro-rata = (Annual Entitlement × Months Remaining) ÷ 12

Example: 20 days entitlement, starts July 1

Pro-rata = (20 × 6) ÷ 12 = 10 days

Implementation

function calculateProRataEntitlement(
  annualDays: number,
  startDate: Date,
  yearEnd: Date
): number {
  const monthsRemaining = differenceInMonths(yearEnd, startDate) + 1;
  return Math.round((annualDays * monthsRemaining) / 12);
}

Balance Adjustments

Manual Adjustments

HR can manually adjust balances:

1. Add days: Bonus leave, correction
2. Remove days: Correction, policy enforcement
3. Adjust carryover: Year-end processing

Audit Trail

All adjustments are logged:

INSERT INTO audit_logs (
  actor_user_id,
  entity_type,
  entity_id,
  action,
  old_values,
  new_values
) VALUES (
  'hr-user-uuid',
  'profiles',
  'employee-uuid',
  'update',
  '{"carryover_days": 3}',
  '{"carryover_days": 5}'
);