请求SQL专家解析appointments_2015至结尾逻辑并协助改写SQL片段

Let me walk you through this logic in plain terms, starting with the lead-in CTE and moving to the final calculation. First, quick context: the current_employees CTE (the one right before appointments_2015) just grabs all employees who are still working at the company (since their end_date is NULL). We need that because the rest of the query only cares about these active employees.

Step 1: The appointments_2015 CTE #

This part filters and adjusts employment records to focus only on the time those active employees spent working in 2015. Let's break it down piece by piece:

Filtering the right records #

First, the JOIN current_employees ce ON a.emp_id = ce.emp_id ensures we only look at records for employees who are still active today. Then the WHERE clause narrows it to records that overlap with 2015:

• start_date < '2016-01-01': The job started before 2016 (so it either started in 2015 or earlier)
• (end_date >= '2015-01-01' OR end_date IS NULL): The job either ended in 2015 or later, or is still ongoing (so it overlaps with at least part of 2015)

Adjusting dates to fit 2015 #

The two CASE statements fix the start and end dates to only cover the 2015 calendar year:

• Start date adjustment: CASE WHEN start_date < '2015-01-01' THEN '2015-01-01' ELSE start_date END
  • If an employee started the job before 2015 (like in 2014), we reset their start date to January 1, 2015—since we only care about their 2015 pay. If they started in 2015, we keep their actual start date.
• End date adjustment: CASE WHEN end_date < '2016-01-01' THEN end_date ELSE '2015-12-31' END
  • If the job ended during 2015, we keep the actual end date. If it ended after 2015 or is still ongoing, we set the end date to December 31, 2015—again, since we only calculate pay up to the end of 2015.

Step 2: The Final SELECT Query #

This part calculates the total prorated salary each active employee earned in 2015:

• GROUP BY emp_id: We're aggregating results per individual employee.
• The calculation SUM( salary * (end_date - start_date + 1) / 365 ) AS total:
  1. end_date - start_date + 1: This counts the number of days the employee was in that role during 2015—the +1 is important (if someone worked Jan 1 to Jan 2, that's 2 days, not 1).
  2. salary / 365: This gives the employee's daily rate (assuming their salary is annual).
  3. Multiply those two to get the prorated pay for that specific job record in 2015.
  4. SUM all those prorated amounts for the employee to get their total 2015 earnings across all roles they held that year.

内容的提问来源于stack exchange，提问作者t.harris