sql - Insert statement with nested Select and inner join -

for clock machine.

this automatically set clock record in table @ midnight people forgot clock out in evening. want insert clock record same clock in time

employee table has usual, employeeid, employeename, clocktypeid clocktypeid quick reference employees current status (working (1) or not working (2)) clock table has clockid, clocktypeid, employeeid, clockdate, clocktime

at moment have (and want do)

insert dbo.clock(employeeid, clocktypeid, clockdate, clocktime)         select employeeid, 2, getdate(), [mininum clock time current day current employee]         employee         employee.clocktypeid <> 2; 

thanks in advance!

edit: bit more info

i want to, when query run in evening; insert row clock table each of employees has clocktypeid of '1' (indicating still clocked in) row want insert, want date current date , time time of first clock in (clocktypeid 1) of day. end clock-in time , date being same clock-out time , date. meaning system have worked 0 hours day should flag superiors can manually correct it.

your description suggests this:

insert dbo.clock(employeeid, clocktypeid, clockdate, clocktime)     select employeeid, 2, cast(getdate() date), min(clocktime)     employee     group employeeid, cast(getdate() date)     having sum(case when clocktypeid = 1 1                     when clocktypeid = 2 -1                     else 0                end) > 0;