计划备份:
DECLARE @strPath NVARCHAR(200)
set @strPath = convert(NVARCHAR(19),getdate(),23)
set @strPath = REPLACE(@strPath, ':' , '.')
set @strPath = 'E:\Backup\' + @strPath +'备份'+ '.bak'
BACKUP DATABASE [jewelry] TO DISK = @strPath WITH NOINIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT
计划存储过程:
EXEC HRAttendsum_update;
exec HRAttend_UpFromFlow_absent;
exec HRAttend_UpFromFlow_AttCard;
exec HRAttend_UpFromFlow_business;
exec HRAttend_UpFromFlow_mobile;
exec HRAttend_UpFromFlow_overtime;
exec HRAttend_UpFromFlow;
sql存储过程:
USE [HRData]
GO
/****** Object: StoredProcedure [dbo].[HRAttendsum_insert] Script Date: 08/25/2022 10:23:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--每天0点执行当天数据的写入
ALTER proc [dbo].[HRAttendsum_insert]
as
-- if(@FDate ='')
declare @FDate varchar(10)
set @FDate=Convert(nvarchar(10),GETDATE(),23)
insert into HRAttendsum (userid,username,Fdate,department,seclevel)
select loginid,lastname,@FDate,departmentid,a.seclevel from EDATA.dbo.hrmresource
a left join HRAttendsum hh on hh.userid=a.loginid and hh.Fdate=@FDate
where hh.userid is null and (loginid like '8888%' or loginid='8000')
--更新用户级别
update a set a.seclevel=b.seclevel from HRAttendsum a
left join EDATA.dbo.hrmresource b on b.loginid=a.userid
--更新日期类别
update dbo.HRAttendsum set day_type=bb.daytype from HRAttendsum
left join (
select a.fdate,a.day_type,
case when b.IsWork=1 and HolidayType=0 then 0
when b.IsWork=0 and HolidayType=0 then 1
when b.IsWork=0 and HolidayType=1 then 2
else 3 End AS 'daytype'
from HRAttendsum a
left join dbo.HRWorkDay b on b.FDate=a.FDate)
bb on bb.FDate=HRAttendsum.Fdate
--select * from HRAttendsum set seclevel=0 Fdate=Convert(nvarchar(10),GETDATE(),23)
--更新日期类别
update HRAttendsum set remarks='节假日' from HRAttendsum
where Day_Type=0 and EndTime is null and StartTime is null
and Fdate>'2016-12-31'