Complicated daily automatic insert query in 2 tables

Question

I have the following tables with their columns (only the relevant columns are listed):

Entry
EntryID (int/auto-inc)
EmployeeNumber (int)
JustifyDate (datetime)
EntryDate (datetime)

Employee
EmployeeNumber (int)
CoreHourID (int)
WorkingFromHome (bit/bool)

Hour
EntryID (int)
InHour (datetime)
OutHour (datetime)

CoreHour
CoreHourID (int) - not unique
InHour (int)
OutHour (int)

EDIT: I had forgotten to mention that CoreHourID isn't a unique field, table can look like this:

+-----------+-------+-------+----------+
|CoreHourId |InHour |OutHour|Identifier|
+-----------+-------+-------+----------+
|    2      |  9    |  12   |    1     |
|    2      |  14   |  17   |    2     |
|    3      |  7    |  11   |    3     |
|    3      |  15   |  18   |    4     |
+-----------+-------+-------+----------+

Sorry for the big layout, I really don't know how to properly post table information. Now here's an attempt at explaining what I'm trying to do:

Every day, a row should be inserted in Entry and Hour for all employees who are WorkingFromHome. In the Entry table it should place the corresponding EmployeeNumber, and for JustifyDate it should add whatever day it is when the job is running. For the EntryDate field it should add that day's date, but the time part should be the InHour from the first corresponding CoreHour row.

For the Hour table it should add the EntryID that just got inserted in the Entry table, and the InHour should be the same as the EntryDate and for the OutHour field it should add a DateTime based on the last OutHour corresponding for the employee's CoreHourID.

I'm struggling a lot, so any help is appreciated.

PS: Any comments/questions regarding my explanation are welcome and I will gladly respond.

Answer 1

The following could be encapsulated into a stored procedure that can be executed via a scheduled job. I'm not exactly sure what you meant by 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

(Revised to use Output clause).