/*
You are managing an employee leave tracking system for an organization. The `employee_leaves` table contains information about employees’ leave requests:  

| Column Name        | Data Type     | Description                                     |  
|--------------------|---------------|-------------------------------------------------|  
| leave_id           | INT           | Unique ID for each leave request                |  
| employee_id        | INT           | Unique ID for each employee                     |  
| leave_start_date   | DATE          | Start date of the leave                         |  
| leave_end_date     | DATE          | End date of the leave                           |  
| leave_status       | VARCHAR(20)   | Status of the leave (Approved/Rejected/Pending) |  

Task:  

1. Calculate the total leave duration (number of days) and the total number of approved leave requests for each employee based on their approved leaves.  
2. Determine whether an employee has exceeded their leave limit. If they have taken more than 10 days of leave, flag it as "Exceeded", otherwise flag it as "Not Exceeded".  
3. Display the following columns:  
   • `employee_id`  
   • `total_approved_days` (sum of all approved leave durations)  
   • `leave_count` (total number of approved leave requests)  
   • `leave_limit` (status indicating whether the leave limit was exceeded or not)   

Expected Output:  

| employee_id | total_approved_days  | leave_count | leave_limit    | 
|-------------|----------------------|-------------|----------------|
| 101         | 16                   | 2           |  Exceeded      |
| 103         |  3                   | 1           |  Not Exceeded  |             
| 104         | 18                   | 3           |  Exceeded      |
*/

CREATE TABLE employee_leaves (
    leave_id INT,
    employee_id INT,
    leave_start_date DATE,
    leave_end_date DATE,
    leave_status VARCHAR(20)
);

INSERT INTO employee_leaves (leave_id, employee_id, leave_start_date, leave_end_date, leave_status) VALUES
(1, 101, '2024-01-01', '2024-01-10', 'Approved'),
(2, 101, '2024-06-15', '2024-06-20', 'Approved'),
(3, 102, '2024-02-05', '2024-02-08', 'Rejected'),
(4, 103, '2024-03-10', '2024-03-12', 'Approved'),
(5, 104, '2024-05-01', '2024-05-07', 'Approved'),
(6, 104, '2024-08-15', '2024-08-20', 'Approved'),
(7, 104, '2024-11-01', '2024-11-05', 'Approved'),
(8, 105, '2024-09-01', '2024-09-02', 'Pending');

SELECT 
    employee_id,
    SUM(DATEDIFF(leave_end_date, leave_start_date) + 1) AS total_approved_days,
    COUNT(leave_id) AS leave_count,
    CASE 
        WHEN SUM(DATEDIFF(leave_end_date, leave_start_date) + 1) > 10 THEN 'Exceeded'
        ELSE 'Not Exceeded'
    END AS Leave_limit
FROM employee_leaves
WHERE leave_status = 'Approved'
GROUP BY employee_id;