# 需要一个跨越新一年的连续使用天数计算-FMLA过期[英] Need a continuous Days Used count that crosses a new year - FMLA Expiration

### 问题描述

SELECT Employee.EmpID, Trippin.EmpUID, Trippin.DU, Employee.FirstName, Employee.LastName, (84-DU) as DR FROM (
SELECT EmpUID, Sum(DaysUsed) as DU FROM (
SELECT EmpUID, SUM(DateDiff(d,StartDate,StopDate)) AS DaysUsed FROM dbo.EmpStatH WHERE EmpStatus = ''A'' AND St​​artDate 不为 NULL 且 StopDate 为 NULL 且 DatePart(yy,StartDate) = DatePart(yy,GetDate()) 按 EmpUID 分组

SELECT EmpUID, SUM(DateDiff(d,(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)),StopDate)) AS DaysUsed FROM dbo.EmpStatH WHERE EmpStatus = ''A'' AND St​​artDate 不是NULL AND St​​opDate 不是 NULL AND DatePart(yy,StartDate) <DatePart(yy,GetDate()) AND DatePart(yy,StopDate) = DatePart(yy,GetDate()) 按 EmpUID 分组

SELECT EmpUID, SUM(DateDiff(d,StartDate,GetDate())) AS DaysUsed FROM dbo.EmpStatH WHERE EmpStatus = ''A'' AND St​​artDate 不为 NULL 且 StopDate 为 NULL 且 DatePart(yy,StartDate) = DatePart(yy,GetDate()) 按 EmpUID 分组
) AS Jumpoff GROUP BY EmpUID) AS Trippin LEFT JOIN Employee ON Trippin.EmpUID = Employee.EmpUID WHERE DU >70 和 DU <86 AND Employee.TermDate IS NULL ORDER BY DU DESC

## 推荐答案

DatePart(yy,StartDate) = DatePart(yy,GetDate())