可以将以下内容封装到存储过程中,该存储过程可以通过计划作业执行。我不太清楚你的意思
for JustifyDate it should add whatever day it is when the job is running
.
Declare @NewEntries Table (
EntryId int not null
, EmployeeNumber int not null
, JustifyDate datetime
, EntryDate datetime
)
Insert [Entry]( EmployeeNumber, JustifyDate, EntryDate )
Output Inserted.EntryId, Inserted.EmployeeNumber, Inserted.JustifyDate, Inserted.EntryDate
Into @NewEntries
Select EmployeeNumber
, CURRENT_TIMESTAMP
, DateAdd(hh, CoreHour.InHour, DateAdd(d, 0, CURRENT_TIMESTAMP))
From Employee
Join CoreHour
On CoreHour.CoreHourId = Employee.CoreHourId
Where WorkingFromHome = 1
Insert [Hour]( EntryId, InHour, OutHour )
Select NE.EntryId, DatePart(hh, NE.EntryDate), CoreHour.OutHour
From @NewEntries As NE
Join Employee
On Employee.EmployeeNumber = NE.EmployeeNumber
Join CoreHour
On CoreHour.CoreHourId = Employee.CoreHourId
(修订为使用输出条款)。
