关于限制extrahours最大值为2及周末补休核算的SQL需求咨询

Let's walk through how to implement your requirements step by step. First, let's restate the core rules to make sure we're aligned:

• Cap weekday extrahours at 2 hours; any amount over this must be offset using weekend compensatory leave.
• For weekend work:
  • If total work hours ≥ 9: treat all overtime as paid overtime.
  • If total work hours < 9: use those hours to offset weekday excess overtime (compensatory leave).

Step 1: Data Preprocessing #

First, we'll clean and enrich the dataset to make calculations easier. We'll use Python's pandas since it's ideal for tabular data manipulation.

import pandas as pd
from datetime import datetime

# Load your dataset
data = pd.DataFrame([
    [1, "2018-05-01", "09:00:00.0000000", "18:50:00.0000000", 0],
    [1, "2018-05-02", "08:00:00.0000000", "20:00:00.0000000", 3],
    [1, "2018-05-03", "09:50:00.0000000", "19:50:00.0000000", 1],
    [1, "2018-05-04", "07:00:00.0000000", "17:45:00.0000000", 1],
    [1, "2018-05-05", "09:15:00.0000000", "13:50:00.0000000", -5],
    [1, "2018-05-06", "08:45:00.0000000", "23:55:00.0000000", 6],
    [1, "2018-05-07", "12:00:00.0000000", "23:00:00.0000000", 2],
    [1, "2018-05-08", "02:30:00.0000000", "23:55:00.0000000", 12],
    [1, "2018-05-09", "10:50:00.0000000", "19:50:00.0000000", 0],
    [1, "2018-05-10", "08:36:00.0000000", "19:50:00.0000000", 2],
], columns=["no", "Date", "time_in", "time_out", "extrahours"])

# Convert date/time columns to proper formats
data["Date"] = pd.to_datetime(data["Date"])
data["time_in"] = pd.to_datetime(data["time_in"]).dt.time
data["time_out"] = pd.to_datetime(data["time_out"]).dt.time

# Flag weekend days (Saturday=5, Sunday=6)
data["is_weekend"] = data["Date"].dt.weekday.isin([5, 6])

# Calculate total daily work hours (handles same-day shifts; adjust if you have cross-night shifts)
def get_work_hours(row):
    shift_start = datetime.combine(row["Date"], row["time_in"])
    shift_end = datetime.combine(row["Date"], row["time_out"])
    duration = shift_end - shift_start
    return round(duration.total_seconds() / 3600, 2)

data["work_hours"] = data.apply(get_work_hours, axis=1)

Step 2: Process Weekday Overtime Excess #

We'll cap weekday extrahours at 2 and calculate how much excess needs to be offset with weekend leave.

# Split data into weekday and weekend subsets
weekdays = data[~data["is_weekend"]].copy()
weekends = data[data["is_weekend"]].copy()

# Calculate excess overtime (over 2 hours) and cap extrahours
weekdays["overtime_excess"] = weekdays["extrahours"].apply(lambda x: max(0, x - 2))
weekdays["adjusted_extrahours"] = weekdays["extrahours"].apply(lambda x: min(x, 2))

# Total excess that needs to be offset
total_excess = weekdays["overtime_excess"].sum()
print(f"Total excess overtime to offset: {total_excess} hours")

Step 3: Process Weekend Work for Compensatory Leave or Paid Overtime #

We'll classify weekend shifts into compensatory leave (for <9 hour shifts) or paid overtime (for ≥9 hour shifts).

# Flag weekend shifts eligible for paid overtime
weekends["eligible_for_overtime_pay"] = weekends["work_hours"] >= 9

# Calculate compensatory leave hours (from short weekend shifts) and paid overtime hours
weekends["compensatory_hours"] = weekends.apply(
    lambda row: row["work_hours"] if not row["eligible_for_overtime_pay"] else 0,
    axis=1
)
weekends["paid_overtime_hours"] = weekends.apply(
    lambda row: row["extrahours"] if row["eligible_for_overtime_pay"] else 0,
    axis=1
)

# Total available compensatory leave
total_compensatory = weekends["compensatory_hours"].sum()
print(f"Total compensatory leave available: {total_compensatory} hours")

Step 4: Offset Excess Overtime with Compensatory Leave #

We'll apply the compensatory leave to reduce the excess overtime, then compile the final results.

# Calculate how much excess we can offset
offset_amount = min(total_excess, total_compensatory)
remaining_excess = total_excess - offset_amount
remaining_compensatory = total_compensatory - offset_amount

# Merge back into final dataset and add final calculations
final_data = pd.concat([weekdays, weekends]).sort_values("Date")

final_data["final_extrahours"] = final_data.apply(
    lambda row: row["adjusted_extrahours"] if not row["is_weekend"] else row["paid_overtime_hours"],
    axis=1
)
final_data["remaining_excess_overtime"] = remaining_excess
final_data["remaining_compensatory_leave"] = remaining_compensatory

Key Notes & Adjustments #

• Cross-night shifts: If you have shifts that span midnight (e.g., 22:00 to 06:00), you'll need to adjust the get_work_hours function to account for the date change.
• Overtime calculation logic: The example uses your provided extrahours values, but if you need to recalculate extrahours based on a standard workday length (e.g., 8 or 9 hours), you can modify the code to compute it from work_hours.
• Offset order: This example uses a total offset approach. If you need to offset excess overtime in chronological order (oldest excess first), you'll need to iterate through weekday records and apply compensatory leave incrementally.

内容的提问来源于stack exchange，提问作者Venkat Vikram