HR辅助工具中用到的SQL语句

select distinct id,Record_DateTime from HR_Record 
Where DATEDIFF(day,record_datetime,'2012-10-9')=0 
AND ID not in(select  ID from EmployyAttRecord where DATEDIFF(day,attdate,'2012-10-9')=0)
		

update EmployyAttRecord set Att0= hr_record.record_time 
from HR_Record ,employyattrecord 
where datename(hour,record_time)='8' and hr_record.id=employyattrecord.id and datediff(day,Record_DateTime,'2012-10-9')=0 and datediff(day,attdate,'2012-10-9')=0

USE [hr]
GO
/****** Object:  StoredProcedure [dbo].[sp_CreatByEmploy_EmployAttRecord]    Script Date: 02/28/2013 23:11:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_CreatByEmploy_EmployAttRecord]
	-- Add the parameters for the stored procedure here
		@Action int,
		@AttDate date		
	
AS
DECLARE
		@returnResult int
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
    -- Action =0 Create new list of current date .
    --        =1 delete old list & Create new list.
    if(@Action=1)
    begin
    delete From EmployyAttRecord where DATEDIFF(day,attdate,@attdate)=0
    end
        
	insert into EmployyAttRecord (id,attdate) select id,@AttDate as AddDate from EmployyTable
	
	Set @returnResult= @@rowcount
	
	insert into EmployyAttRecord (id,attdate) 
	select distinct id,@AttDate from HR_Record 
	Where DATEDIFF(day,record_datetime,@Attdate)=0 
		  AND ID not in(select ID from EmployyAttRecord where DATEDIFF(day,attdate,@Attdate)=0)
		
	set @returnResult=@returnResult+@@ROWCOUNT
	
	return @returnResult
	
END

select * from HR_Record ,employyattrecord 
where datename(hour,record_time)='8' and hr_record.id=employyattrecord.id and datediff(day,Record_DateTime,'2012-10-9')=0 and datediff(day,attdate,'2012-10-9')=0
order by EmployyAttRecord.id