SQL查询:在2个条目中的日期之间等待的时间

Question

我有一个为给定员工选择最近加薪的查询：

SELECT TOP 1 Raises.EmployeeID, Raises.RaiseAmt, Raises.Date, Raises.SalaryPrior
FROM Raises
WHERE (((Raises.EmployeeID)=[Enter Employee ID: ]))
ORDER BY Raises.Date DESC;

对于多次加薪的员工，我如何显示第一次加薪和第二次加薪之间的时间量？

Answer 1

看起来您正在使用SQL Server。您可以使用一个公用表表达式来简化这一过程：

WITH recent_raises AS (
    SELECT TOP 2 Raises.EmployeeID, Raises.RaiseAmt, Raises.Date, Raises.SalaryPrior
    FROM Raises
    WHERE (((Raises.EmployeeID)=[Enter Employee ID: ]))
    ORDER BY Raises.Date DESC;
),
SELECT DATEDIFF(day, r1.Date, r2.Date) AS elapsed_days
FROM recent_raises r1
    JOIN recent_raises r2
        ON r1.Date < r2.Date

Answer 2

尝试此查询(仅当当前员工至少有两次加薪时才有效)：

SELECT  y.*, DATEDIFF(DAY, y.[2], y.[2]) AS DaysBetweenTwoRaises
FROM
(
SELECT TOP(2) Raises.Date, ROW_NUMBER() OVER(ORDER BY Raises.Date DESC) RowNum
FROM Raises
WHERE Raises.EmployeeID=[Enter Employee ID: ]
ORDER BY Raises.Date DESC
) x
PIVOT ( MAX(x.Date) FOR x.RowNum IN ([1], [2]) ) y;