时间戳

首页 > 解决方案 > 如何按季度获得在组织中完成 5 年的员工

sql - 如何按季度获得在组织中完成 5 年的员工

问题描述

我正在努力让完成 5、10、15、20 等年的员工。我想按季度检索数据，所以我在 where 子句中添加了 switch 语句

declare @CurrentDate date

set @CurrentDate=GETDATE() ;

select EmployeeID,FirstName+''+isnull(MiddleName,'')+''+LastName as EmployeeName,JoiningDate,DATEDIFF(year, isnull(JoiningDate,''),getdate()) Experience

 from HRIS.dbo.EmpMaster
 where 
 ActiveFlag=1 and
CASE
    WHEN  DATEPART(quarter, getdate()) =1 and month(JoiningDate) BETWEEN 1 and 3 then 1
    WHEN  DATEPART(quarter, getdate()) =2 and month(JoiningDate) BETWEEN 4 and 6 then 1
    WHEN  DATEPART(quarter, getdate()) =3 and month(JoiningDate) BETWEEN 7 and 9 then 1
    WHEN  DATEPART(quarter, getdate()) =4 and month(JoiningDate) BETWEEN 10 and 12 then 1
    else 0
    END
 and  DATEDIFF(year, isnull(JoiningDate,''),@CurrentDate)%5=0 and Year(JoiningDate)!=2019

我收到如下错误

Error: An expression of non-boolean type specified in a context where a condition is expected, near 'and'.

我理解错误是因为 where 子句中的情况没有返回布尔值。

如何编写开关盒以使其返回布尔值

先感谢您！

标签： sqlstored-procedures

解决方案

嗯。. . 为什么不简化呢？

WHERE ActiveFlag = 1 AND
      DATEPART(quarter, getdate()) = DATEPART(quarter, JoiningDate)

这似乎要简单得多。

您的问题将通过= 1在END. 但我不推荐这种方法。

推荐阅读