帮忙看看这个SQL语句，很急，100%结贴！

Question

需求，以及举例数据：打卡记录表结构：
empid，int ,工号
redate,datetime，记录日期
acout_normal，下班时间
acout,datetime，下班实际打卡日期
acout_status,状态
举例数据：
empid recdate                 acout_normal         acout                 acout_status checkatt
328 2008/12/3 0:00:00 2008/12/3 18:00:00 2008/12/3 18:48:52 正常出勤 0
328 2008/11/30 0:00:00 2008/11/30 18:00:00 1900/1/1 0:00:00 旷工         0
328 2008/12/9 0:00:00 2008/12/9 18:00:00 2008/12/9 18:46:33 正常出勤 0
328 2008/11/26 0:00:00 2008/11/26 18:00:00 2008/11/26 18:33:49 正常出勤 0
328 2008/11/27 0:00:00 2008/11/27 18:00:00 2008/11/27 12:04:43 早退         0
328 2008/11/28 0:00:00 2008/11/28 18:00:00 2008/11/28 14:41:00 早退         0
328 2008/11/29 0:00:00 2008/11/29 18:00:00 1900/1/1 0:00:00 旷工         0
节日表tb_holiday：
holidayDay,datetime
holidayName,varchar(50) 
holidaystatus,varchar(50) 举例：
2008/11/30 0:00:00   某节日  正常休息
2008/11/29 0:00:00   某节日  正常休息要得到：
empid recdate                 acout_normal         acout                 acout_status 
328 2008/12/3 0:00:00 2008/12/3 18:00:00 2008/12/3 18:48:52 正常出勤 
328 2008/11/30 0:00:00 2008/11/30 18:00:00 1900/1/1 0:00:00 正常休息   
328 2008/12/9 0:00:00 2008/12/9 18:00:00 2008/12/9 18:46:33 正常出勤 
328 2008/11/26 0:00:00 2008/11/26 18:00:00 2008/11/26 18:33:49 正常出勤 
328 2008/11/27 0:00:00 2008/11/27 18:00:00 2008/11/27 12:04:43 早退        
328 2008/11/28 0:00:00 2008/11/28 18:00:00 2008/11/28 14:41:00 早退        
328 2008/11/29 0:00:00 2008/11/29 18:00:00 1900/1/1 0:00:00 正常休息    
我用下面得语句得不到正确得结果：select empid,
recdate,
acout,
acout_status=(case when tb_holiday.holidayname is null then acin_status else tb_holiday.holidaystatus end),
    convert(int,checkatt) as checkatt
from attendance left join tb_holiday
on attendance.recdate=tb_holiday.holidayDay谢谢！

Answer 1

 是 acout_status数据不能正确获取

Answer 2

select empid,
    recdate,
    acout,
    acout_status=(case when exists(select 1 from tb_holiday where a.recdate= holidayDay) then '正常休息' else  a.acout_status end),
    convert(int,checkatt) as checkatt
from attendance 

Answer 3

select * from attendence where recdate  not in(select holidayDay in tb_holiday);

Answer 4

select * from attendence where recdate  not in(select holidayDay in tb_holiday);

Answer 5

select 
empid,
recdate,
acout,
acout_status=(case when b.holidayname is null then acin_status else b.holidaystatus end)
from attendance a
left join tb_holiday b
on a.recdate=b.holidayDay

Answer 6

if object_id('ta')is not null drop table ta
go
create table ta(empid int,    recdate datetime, acout_normal datetime,            acout datetime,                    acout_status nvarchar(10),    checkatt varchar(10))
insert ta select 328   , '2008/12/3 0:00:00'  ,  '2008/12/3 18:00:00'    ,'2008/12/3 18:48:52' ,   N'正常出勤'   , 0
insert ta select 328   , '2008/11/30 0:00:00' ,   '2008/11/30 18:00:00',    '1900/1/1 0:00:00'  ,  N'旷工'            ,0
insert ta select 328   , '2008/12/9 0:00:00'    ,'2008/12/9 18:00:00'    ,'2008/12/9 18:46:33'  ,  N'正常出勤'    ,0
insert ta select 328  ,  '2008/11/26 0:00:00'  ,  '2008/11/26 18:00:00'  ,  '2008/11/26 18:33:49' ,   N'正常出勤',    0
insert ta select 328  ,  '2008/11/27 0:00:00'  ,  '2008/11/27 18:00:00'  ,  '2008/11/27 12:04:43'  ,  N'早退'         ,   0
insert ta select 328 ,   '2008/11/28 0:00:00'  ,  '2008/11/28 18:00:00' ,   '2008/11/28 14:41:00'  ,  N'早退'         ,   0
insert ta select 328,    '2008/11/29 0:00:00'   , '2008/11/29 18:00:00',    '1900/1/1 0:00:00'   , N'旷工'            ,0
if object_id('tb')is not null drop table tb
go
create table tb(holidayDay datetime,holidayName varchar(50) ,holidaystatus varchar(50))
insert tb select '2008/11/30 0:00:00' ,  N'某节日',     N'正常休息'
insert tb select '2008/11/29 0:00:00',   N'某节日',     N'正常休息'
SELECT A.EMPID,A.RECDATE,A.ACOUT_NORMAL,A.ACOUT,ISNULL(B.holidaystatus,A.acout_status) FROM TA A LEFT JOIN TB B ON A.recdate=B.holidayDay
/*EMPID       RECDATE                                                ACOUT_NORMAL                                           ACOUT                                                                                                     
----------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ -------------------------------------------------- 
328         2008-12-03 00:00:00.000                                2008-12-03 18:00:00.000                                2008-12-03 18:48:52.000                                正常出勤
328         2008-11-30 00:00:00.000                                2008-11-30 18:00:00.000                                1900-01-01 00:00:00.000                                正常休息
328         2008-12-09 00:00:00.000                                2008-12-09 18:00:00.000                                2008-12-09 18:46:33.000                                正常出勤
328         2008-11-26 00:00:00.000                                2008-11-26 18:00:00.000                                2008-11-26 18:33:49.000                                正常出勤
328         2008-11-27 00:00:00.000                                2008-11-27 18:00:00.000                                2008-11-27 12:04:43.000                                早退
328         2008-11-28 00:00:00.000                                2008-11-28 18:00:00.000                                2008-11-28 14:41:00.000                                早退
328         2008-11-29 00:00:00.000                                2008-11-29 18:00:00.000                                1900-01-01 00:00:00.000                                正常休息*/
 

Answer 7

------------------------------------
-- Author: happyflystone  
-- Version:V1.001  
-- Date:2009-01-22 15:15:12
-------------------------------------- Test Data: attendance
If object_id('attendance') is not null 
    Drop table attendance
Go
Create table attendance(empid int,recdate smalldatetime,acout_normal datetime,acout smalldatetime,acout_status nvarchar(4),checkatt nvarchar(1))
Go
Insert into attendance
select 328,'2008-12-3','2008-12-3 18:00:00','2008-12-3','正常出勤','0' union all
select 328,'2008-11-30','2008-11-30 18:00:00','1900-1-1','旷工','0' union all
select 328,'2008-12-9','2008-12-9 18:00:00','2008-12-9','正常出勤','0' union all
select 328,'2008-11-26','2008-11-26 18:00:00','2008-11-26','正常出勤','0' union all
select 328,'2008-11-27','2008-11-27 18:00:00','2008-11-27','早退','0' union all
select 328,'2008-11-28','2008-11-28 18:00:00','2008-11-28','早退','0' union all
select 328,'2008-11-29','2008-11-29 18:00:00','1900-1-1','旷工','0' 
G
Go
-- Test Data: tb_holiday
If object_id('tb_holiday') is not null 
    Drop table tb_holiday
Go
Create table tb_holiday(holidayDay datetime,holidayName nvarchar(3),holidaystatus nvarchar(4))
Go
Insert into tb_holiday
 select '2008-11-30 0:00:00','某节日','正常休息' union all
 select '2008-11-29 0:00:00','某节日','正常休息' 
Go
--Start
select empid,
    recdate,
    acout,
    acout_status=(case when exists(select 1 from tb_holiday where a.recdate= holidayDay) then '正常休息' else  a.acout_status end),
    convert(int,checkatt) as checkatt
from attendance  a--Result:
/*empid       recdate                                                acout                                                  acout_status checkatt    
----------- ------------------------------------------------------ ------------------------------------------------------ ------------ ----------- 
328         2008-12-03 00:00:00                                    2008-12-03 00:00:00                                    正常出勤         0
328         2008-11-30 00:00:00                                    1900-01-01 00:00:00                                    正常休息         0
328         2008-12-09 00:00:00                                    2008-12-09 00:00:00                                    正常出勤         0
328         2008-11-26 00:00:00                                    2008-11-26 00:00:00                                    正常出勤         0
328         2008-11-27 00:00:00                                    2008-11-27 00:00:00                                    早退           0
328         2008-11-28 00:00:00                                    2008-11-28 00:00:00                                    早退           0
328         2008-11-29 00:00:00                                    1900-01-01 00:00:00                                    正常休息         0（所影响的行数为 7 行）
*/
--End 

Answer 8

select empid,recdate,acout,acout_status=(case when exists(select 1 from tb_holiday where      a.recdate=holidayDay) then '正常休息' else  a.acout_status end),convert(int,checkatt) as checkatt from attendance  a