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

最新推荐文章于 2022-09-21 16:46:20 发布

原创最新推荐文章于 2022-09-21 16:46:20 发布 · 683 阅读

0 ·

CC 4.0 BY-SA版权

C Sharp 专栏收录该内容

51 篇文章

订阅专栏

本文介绍了一种通过SQL查询来实现员工考勤记录与考勤表之间的数据同步及更新的方法。具体步骤包括：从HR_Record表中选择特定日期的记录，并确保这些记录不在EmployyAttRecord表中；然后更新EmployyAttRecord表中的考勤信息；最后提供了一个存储过程用于创建或更新指定日期的考勤记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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