生成sql以查找不在给定日期工作的员工 [英] To generate the sql to find employee who are not working for the given date
问题描述
我有下表和 它的记录， 我想创建一个sql如果 dayoffHrs> 0或员工Hrs = 0 对于给定的日期，那么它应该是 返回为"是"
I have the following table and its record, I want to create an sql if the dayoffHrs >0 or Employee Hrs =0 for the date given, then it should be returned as 'Yes'
例如
声明@Date日期时间
declare @Date datetime
设置@Date = '04 -17-2019'
set @Date = '04-17-2019'
选择 EMPID，  "是  as DayOffYes
Select EmpId, 'Yes' as DayOffYes
来自dayoffhrs，其中DayOffHrs> 0和Attdate = @Date
from dayoffhrs where DayOffHrs>0 and Attdate = @Date
联盟
选择EmpID，来自员工的'是'作为DayOffYes
Select EmpID, 'Yes' as DayOffYes
 情况何时 DATEPART（dw，@ Date ）= 4 和    WedHrs大于0 或DATEPART（dw，@ Date ）= 3和Tuehrs> 0
from Employee where Case when DATEPART(dw,@Date ) = 4 and WedHrs>0 Or DATEPART(dw,@Date ) = 3 and TueHrs >0
请 帮助，我已经给出了表结构。
Please help, I have given the table structure.
非常感谢
Pol
Create table Employee
(Empid int, EmpName varchar(max), MonHrs decimal(5,2), TueHrs decimal(5,2), WedHrs decimal(5,2) , ThuHrs decimal(5,2), FriHrs decimal(5,2), SatHrs decimal(5,2))
Empid EmpName MonHrs TueHrs WedHrs ThuHrs FriHrs SatHrs
20 XXXX 8 8 0 8 0 0
13 YYYY 8 0 8 8 8 8
15 ZZZZ 9 9 0 9 9 9
Create table DayOff
(Empid int, attdate datetime, dayoffHrs(5,2))
Record
EmpID Attdate DayOffHrs
20 01-01-2019 8
20 07-07-2019 8
13 10-06-2019 7.30
13 13-06-2019 7.30
15 02-01-2019 9.00
15 08-01-2019 4.30
polachan
推荐答案
声明@Date datetime
declare @Date datetime
set @Date = '04 -17-2019'
set @Date = '04-17-2019'
if exists（
If exists (
选择 *
Select *
from dayoffhrs加入员工 ; on dayoffhrs.empid = employee。 < span style ="color：＃333333; font-family：'Segoe UI'，'Lucida Grande'，Verdana，Arial，Helvetica，sans-serif; font-size：14px"> empid
其中DayOffHrs> 0和Attdate = @Date
where DayOffHrs>0 and Attdate = @Date
和1 = DATEPART（dw，@ Date ）= 4
WedHrs> 0 或DATEPART（dw，@ Date ）= 3和Tuehrs> 0
然后1其他0结束
）
begin
begin
打印'是'
end
end
这篇关于生成sql以查找不在给定日期工作的员工的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
