sql - join with earliest child record -

i have ms sqlserver 2008 database 2 tables, worktodo , workdone:

create table worktodo (     workorder varchar(32),     worker varchar(64),     duedate datetime,     primary key workorder ); create table workdone (     workorder varchar(32),     donedate datetime ); 

and i'm looking query return, each worker, total number of worktodos, , total number of late worktodos, late defined donedate > duedate, or duedate > , there no workdone record.

trivial in code, need query can run rather simple-minded reporting tool.

any ideas?

edited: added sample data:

worktodo: workorder  worker  duedate 10001      joe     2012-01-01 10002      joe     2012-01-02 10003      fred    2012-01-03 10004      bill    2013-12-31  workdone: worker     donedate 10001      2011-01-01 10002      2011-12-30 10002      2012-01-04  desired: worker   num_total_workorders   num_late_workorders bill     1                      0 fred     1                      1 joe      2                      1 

bill has 1 worktodo, has no child workdones. because duedate in future, has 1 workorder , no late workorders.

fred has 1 worktodo, has no child workdones. because duedate in past, has 1 workorder , 1 late workorder.

joe has 2 worktodos. workorder 10001 had duedate of 2012-01-01 wasn't done until 2012-01-31, late. workorder 10002 had duedate of 2012-01-02, it's earliest workdone done prior that, on 2011-12-30, not late. there workdone 10002 donedate later duedate has no relevance. results in joe having 2 workorders, , 1 late workorder.

(also - assume workorder primary key, on worktodo).

you can use sum(case comparison 1 else 0 end) total cases comparison true. this. i'm making assumption there ever 1 workdone record given workorder.

select worktodo.worker, count(worktodo.workorder) totalworktodo,     sum(case when (workdone.workorder null , duedate < getdate() )         or donedate > duedate     1 else 0 end) totallateworktodo worktodo left outer join workdone     on worktodo.workorder = workdone.workorder group worktodo.worker 

if multiple workdone records given workorder.

select worktodo.worker, count(worktodo.workorder) totalworktodo,     sum(case when (workdone.workorder null , duedate < getdate() )         or donedate > duedate     1 else 0 end) totallateworktodo worktodo left outer join      (select workorder, min(donedate) donedate workdone         group workorder) workdone     on worktodo.workorder = workdone.workorder group worktodo.worker