Monthly FTE CTE Code Snippet

WITH monthly_fte AS (
  SELECT
    employee_id,
    employee_name,
    @month_start AS month_start,
    @month_end AS month_end,
    CASE
      -- Secondary Status: Seconded or Transferred Out
      WHEN secondary_status IN ('Seconded out to', 'Transferred out to') THEN
        CASE
          WHEN position_start_date >= @month_start AND position_start_date <= @month_end AND position_end_date > @month_end THEN
            ROUND(DATEDIFF(DAY, position_start_date, @month_end) * 1.0 / @days_in_month * fte_contract, 2)
          WHEN position_end_date BETWEEN @month_start AND @month_end THEN
            ROUND(DATEDIFF(DAY, @month_start, position_end_date) * 1.0 / @days_in_month * fte_contract, 2)
          WHEN position_start_date >= @month_end AND employment_start_date < @month_start THEN
            fte_contract
          WHEN position_start_date < @month_start AND position_end_date > @month_end THEN
            NULL
          WHEN position_start_date < @month_start AND position_end_date BETWEEN @month_start AND @month_end THEN
            ROUND(DATEDIFF(DAY, position_end_date, @month_start) * 1.0 / @days_in_month * fte_contract, 2)
          WHEN position_end_date < @month_start THEN
            fte_contract
          ELSE
            NULL
        END

      -- Leave types: Zero FTE
      WHEN secondary_status IN ('Maternity Leave', 'Adoption Leave', 'Long Term Sick', 'Sabbatical', 'Parental Leave') THEN
        0.0

      -- Currently employed but status varies
      ELSE
        CASE
          WHEN employment_start_date <= @month_start AND employment_end_date IS NULL AND position_end_date IS NULL THEN
            fte_contract
          WHEN employment_start_date <= @month_start AND employment_end_date > @month_end THEN
            fte_contract
          WHEN employment_start_date >= @month_end OR employment_start_date IS NULL THEN
            NULL
          WHEN employment_start_date BETWEEN @month_start AND @month_end THEN
            ROUND(DATEDIFF(DAY, employment_start_date, @month_end) * 1.0 / @days_in_month * fte_contract, 2)
          WHEN employment_status = 'Joiner & Leaver' AND employment_end_date < @month_start THEN
            NULL
          WHEN employment_end_date BETWEEN @month_start AND @month_end THEN
            ROUND(DATEDIFF(DAY, employment_end_date, @month_start) * 1.0 / @days_in_month * fte_contract, 2)
          WHEN position_end_date BETWEEN @month_start AND @month_end THEN
            ROUND(DATEDIFF(DAY, position_end_date, @month_start) * 1.0 / @days_in_month * fte_contract, 2)
          WHEN position_end_date IS NULL AND position_start_date <= @month_start THEN
            fte_contract
          ELSE
            NULL
        END
    END AS fte_month
  FROM employee_data_table
)
SELECT *
FROM monthly_fte;