基于现有SQL表结构计算员工月度休假余额及休假类型统计

Hey there! Let's work through how to calculate the monthly leave days each employee has taken, grouped by leave type. First, let's confirm your existing table structure and test data to make sure we're aligned:

Existing Schema & Test Data #

CREATE TABLE Employee ( EmpID Int Primary Key, Name Varchar(25) );
CREATE TABLE LeaveBalance ( BalanceID Int Primary Key, Balance Int, EmpID Int FOREIGN KEY REFERENCES Employee(EmpID) );
CREATE TABLE Leave ( ID Int, LeaveType Varchar(25), AppliedOn Date, ApprovedOn Date, EmpID Int FOREIGN KEY REFERENCES Employee(EmpID), BalanceID Int FOREIGN KEY REFERENCES LeaveBalance(BalanceID) );

INSERT INTO Employee VALUES (1, 'Erick')
INSERT INTO Employee VALUES (2, 'John')
INSERT INTO LeaveBalance VALUES (1, 25, 1)
INSERT INTO LeaveBalance VALUES (2, 15, 2)
INSERT INTO Leave VALUES (1, 'Sick Leave', '2017-12-21', '2017-12-22', 1, 1)
INSERT INTO Leave VALUES (2, 'Casual Leave', '2017-12-26', '2017-12-26', 1, 1)
INSERT INTO Leave VALUES (3, 'Sick Leave', '2018-01-11', '2018-01-11', 1, 1)
INSERT INTO Leave VALUES (4, 'Casual Leave', '2017-12-26', '2017-12-26', 2, 2)
INSERT INTO Leave VALUES (5, 'Earned Leave', '2018-01-11', '2018-01-11', 2, 2)

Solution Query #

Assuming we want to count approved leave days (using ApprovedOn as the date for monthly grouping), here's a query that will give you the clear monthly breakdown per employee and leave type:

SELECT
    e.EmpID,
    e.Name,
    DATE_FORMAT(l.ApprovedOn, '%Y-%m') AS Month_Year,
    l.LeaveType,
    COUNT(*) AS Leave_Days -- Note: This assumes each leave record equals 1 day; adjust if records span multiple days
FROM
    Employee e
JOIN
    Leave l ON e.EmpID = l.EmpID
WHERE
    l.ApprovedOn IS NOT NULL -- Only include leaves that have been approved
GROUP BY
    e.EmpID, e.Name, DATE_FORMAT(l.ApprovedOn, '%Y-%m'), l.LeaveType
ORDER BY
    e.EmpID, Month_Year, l.LeaveType;

Quick Breakdown #

• Monthly Grouping: DATE_FORMAT(l.ApprovedOn, '%Y-%m') extracts the year and month from the approval date, so we can aggregate leaves by each calendar month.
• Leave Day Count: This query assumes each row in the Leave table represents 1 day of leave. If your leave records span multiple days (e.g., a leave from 2018-01-01 to 2018-01-03), you'd need to calculate the day difference instead: DATEDIFF(l.EndDate, l.StartDate) + 1 AS Leave_Days (you'd first need to add start/end date columns to the Leave table for this).
• Approval Filter: The WHERE l.ApprovedOn IS NOT NULL ensures we only count leaves that have been approved—unapproved requests shouldn't count towards taken leave, right?
• Readable Sorting: The ORDER BY clause organizes results by employee ID, month/year, and leave type to make the output easy to scan.

Sample Output #

Running this query with your test data would return:

If you need to include employees who didn't take any leave in a month (to show zero days), we'd need to generate a list of all months/employees and left join with the leave data—just let me know if you want that adjustment!

内容的提问来源于stack exchange，提问作者Doonie Darkoo