需要连续跨天的“已用天数"计数-FMLA到期 [英] Need a continuous Days Used count that crosses a new year - FMLA Expiration

问题描述

当前查询不会保留2011年开始使用EmpStatus ="A"的天数(DU).它只是在2012年获得新记录.有什么建议吗?

以DR FROM(
)选择Employee.EmpID，Trippin.EmpUID，Trippin.DU，Employee.FirstName，Employee.LastName，(84-DU) SELECT EmpUID，Sum(Da​​ysUsed)作为DU FROM(
SELECT EmpUID，SUM(DateDiff(d，StartDate，StopDate))从dbo.EmpStatH中使用的天数EmpStatus =``A''AND St​​artDate不为空且StopDate为NULL并且DatePart(yy，StartDate)= DatePart(yy，GetDate ())GROUP BY EmpUID
UNION
选择EmpUID，SUM(DateDiff(d，(DATEADD(yy，DATEDIFF(yy，0，getdate())，0))，StopDate))从dbo.Used天开始使用.EmpStatH WHERE EmpStatus =``A''并且StartDate不是NULL AND St​​opDate不是NULL AND DatePart(yy，StartDate)< DatePart(yy，GetDate())和DatePart(yy，StopDate)= DatePart(yy，GetDate())GROUP BY EmpUID
UNION
SELECT EmpUID，SUM(DateDiff(d，StartDate，GetDate()))从dbo.EmpStatH开始使用的天数EmpStatus =``A''AND St​​artDate不为空且StopDate为NULL并且DatePart(yy，StartDate)= DatePart(yy) ，GetDate())GROUP BY EmpUID
)作为EmpUID的AS Jumpoff GROUP)作为Trippin的左联接JOIN员工在Trippin.EmpUID = Employee.EmpUID的位置DU> 70 AND DU< 86 AND Employee.TermDate是NULL DE BY DU DESC

Current query doesn''t carry over Days Used (DU) who started EmpStatus = ''A'' in 2011. It is only picking up new records in 2012. Any suggestions?

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 StartDate IS NOT NULL AND StopDate IS NULL AND DatePart(yy,StartDate) = DatePart(yy,GetDate()) GROUP BY EmpUID
UNION
SELECT EmpUID, SUM(DateDiff(d,(DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)),StopDate)) AS DaysUsed FROM dbo.EmpStatH WHERE EmpStatus = ''A'' AND StartDate IS NOT NULL AND StopDate IS NOT NULL AND DatePart(yy,StartDate) < DatePart(yy,GetDate()) AND DatePart(yy,StopDate) = DatePart(yy,GetDate()) GROUP BY EmpUID
UNION
SELECT EmpUID, SUM(DateDiff(d,StartDate,GetDate())) AS DaysUsed FROM dbo.EmpStatH WHERE EmpStatus = ''A'' AND StartDate IS NOT NULL AND StopDate IS NULL AND DatePart(yy,StartDate) = DatePart(yy,GetDate()) GROUP BY EmpUID
) AS Jumpoff GROUP BY EmpUID) AS Trippin LEFT JOIN Employee ON Trippin.EmpUID = Employee.EmpUID WHERE DU > 70 AND DU < 86 AND Employee.TermDate IS NULL ORDER BY DU DESC

推荐答案

我建​​议重新考虑/研究以下语句:

DatePart(yy，StartDate)= DatePart(yy，GetDate())

上面的语句将查询限制为记录谁的年份为当前年份(在本例中为2012)，因此您将不会获得2011年的任何结果.

I would suggest rethinking / investigating the statement below:

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

The above statement is limiting the query to records who''s year is the current year (in this case 2012), hence you will not get any results for 2011.

这篇关于需要连续跨天的“已用天数"计数-FMLA到期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！