如何根据聘用日期计算假期累计
如何根据聘用日期计算假期累计
提问于 2013-07-16 21:59:29
我需要计算每个员工的假期总时数。很容易找到一个员工
SELECT PRCo, Employee, SUM(dbo.PRTH.Hours) AS TotalHrs,
dbo.PREH.HireDate
FROM A
WHERE A.ActiveYN = 'Y') AND (A.EarnCode IN (5249, 5257, 5258, 5279, 5286, 5296, 5309, 7711, 7733)) AND (A. PostDate <= CONVERT(DATETIME, '2013-07-15 00:00:00', 102)) AND (A.PostDate >= CONVERT(DATETIME, '2012-08-21 00:00:00', 102))
GROUP BY A.PRCo, A.Employee, A.PREH.HireDate
HAVING (A.Employee = 1)我有一个很难得到所有员工的时间，因为发布日期范围是不同的基础上的招聘日期。。我在考虑使用游标或循环，但我确定它到底是如何工作的，否则它将会工作。任何建议都会得到重视。我使用t-sql。谢谢
回答 2
Stack Overflow用户
发布于 2013-07-16 22:23:58
您可以使用Employee_Id和Post_Date值创建临时表。在主查询中，将这个临时表与员工id连接起来，并获取post日期值。以下是一个示例查询：
选择
PRCo, Employee, SUM(dbo.PRTH.Hours) AS TotalHrs, dbo.PREH.HireDate 从…
A JOIN #TEMP B ON A.Employee=B.Employee\_Id WHERE A.ActiveYN = 'Y') AND (A.EarnCode IN (5249, 5257, 5258, 5279, 5286, 5296, 5309, 7711, 7733)) AND (A. PostDate <= CONVERT(DATETIME, B.Post\_Date, 102)) AND (A.PostDate >= CONVERT(DATETIME, B.Post\_Date, 102)) GROUP BY A.PRCo, A.Employee, A.PREH.HireDate HAVING (A.Employee = 1)Stack Overflow用户
发布于 2013-07-16 23:25:05
我希望这段代码能有所帮助：
CREATE TABLE #TEMP(EMPLOYEE_ID INT，POST_DATE DATETIME，HOURS INT) INSERT INTO #TEMP VALUES(1，'8/21/2012'，DATEDIFF(HH，'8/21/2012'，GETDATE() INSERT INTO #TEMP VALUES(2，'5/7/2012'，DATEDIFF(HH，'8/21/2012'，GETDATE() SELECT
PRCo, Employee, SUM(dbo.PRTH.Hours) AS TotalHrs, dbo.PREH.HireDate 从…
A JOIN #TEMP B ON A.Employee=B.Employee\_Id WHERE A.ActiveYN = 'Y') AND (A.EarnCode IN (5249, 5257, 5258, 5279, 5286, 5296, 5309, 7711, 7733)) AND (A. PostDate <= CONVERT(DATETIME, B.Post\_Date, 102)) AND (A.PostDate >= CONVERT(DATETIME, B.Post\_Date, 102)) GROUP BY A.PRCo, A.Employee, A.PREH.HireDate HAVING (A.Employee = 1)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/17678601
复制相关文章
相似问题
腾讯云开发者
