SQL Server

-- 我用了JobID来判断女性原来属于55还是50岁退休（即管理和非管理岗）
WITH
	-- 原始退休日期
	ORIGINALINFO AS (
		SELECT EMPLOYEEID, IIF(GENDER = 'Male', 60, IIF(JOBID <= 8, 55, 50)) AS ORAGE,                                     --原退休年龄（用职位判断女性是干部/非干部）
            DATEADD(DAY, 1, DATEADD(YEAR, IIF(GENDER = 'Male', 60, IIF(JOBID <= 8, 55, 50)), BIRTHDAY)) AS ORDATE, --原退休日期
            IIF(GENDER = 'Male', 3, IIF(JOBID <= 8, 3, 5)) * 60 AS MAXDELAY,                                       -- 最大延迟约束
            IIF(GENDER = 'Male', 4.0, IIF(JOBID <= 8, 4.0, 2.0)) AS DELAYRULE,                                     -- 延迟规则
            CEILING(DATEDIFF(MONTH, '2025-01-01', DATEADD(YEAR, IIF(GENDER = 'Male', 60, IIF(JOBID <= 8, 55, 50)), BIRTHDAY)) /
                IIF(GENDER = 'Male', 4.0, IIF(JOBID <= 8, 4.0, 2.0))) AS DELAYAMOUNT
        FROM [YOUR_EMPLOYEE_TABLE]
        ),
	DELAYMONTH AS (
		SELECT A.EMPLOYEEID, IIF(B.DELAYAMOUNT < 0, 0, IIF(B.DELAYAMOUNT > B.MAXDELAY, B.MAXDELAY, B.DELAYAMOUNT)) AS DELAYMONTHS
        FROM [YOUR_EMPLOYEE_TABLE] A
        INNER JOIN ORIGINALINFO B ON A.EMPLOYEEID= B.EMPLOYEEID
        )
SELECT A.NAME, A.GENDER, A.BIRTHDAY, B.ORAGE, B.ORDATE, B.DELAYRULE, B.DELAYAMOUNT,
       DATEADD(DAY, 1, DATEADD(MONTH, B.ORAGE * 12 + C.DELAYMONTHS, A.BIRTHDAY)) AS RETIREMENTDATE
FROM [YOUR_EMPLOYEE_TABLE] A
INNER JOIN ORIGINALINFO B ON A.EMPLOYEEID= B.EMPLOYEEID
INNER JOIN DELAYMONTH C ON A.EMPLOYEEID= C.EMPLOYEEID