CODEWITHSUNDEEP
sqlsql-server
Jeff Dege
Jeff Dege

Reputation: 11720

join with earliest child record

I have a MS SqlServer 2008 database with two tables, worktodo and 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 for a query that will return, for each worker, the total number of worktodos, and the total number of late worktodos, where late is defined as donedate > duedate, or duedate > NOW and there is no workdone record.

Trivial to do in code, but I need a query that can be run by a 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 one worktodo, which has no child workdones. But because the duedate is in the future, so he has one workorder and no late workorders.

Fred has one worktodo, which has no child workdones. But because the duedate is in the past, so he has one workorder and one late workorder.

Joe has two worktodos. Workorder 10001 had a duedate of 2012-01-01 but it wasn't done until 2012-01-31, so it is late. Workorder 10002 had a duedate of 2012-01-02, but it's earliest workdone was done prior to that, on 2011-12-30, so it is not late. That there is another workdone for 10002 with a donedate that is later than the duedate has no relevance. Which results in Joe having two workorders, and one late workorder.

(Also - assume workorder is primary key, on worktodo).

Upvotes: 2

Views: 93

Answers (1)

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

You can use SUM(CASE comparison THEN 1 ELSE 0 END) to total all cases where comparison is true. Like this. I'm making the assumption that there will only ever be one workdone record for a given workorder. 

SELECT worktodo.worker, COUNT(worktodo.workorder) AS TotalWorkToDo,
    SUM(CASE WHEN (workdone.workorder IS NULL AND duedate < getdate() )
        OR donedate > duedate
    THEN 1 ELSE 0 END) AS TotalLateWorkToDo
FROM worktodo
LEFT OUTER JOIN workdone
    ON worktodo.workorder = workdone.workorder
GROUP BY worktodo.worker

If multiple workdone records for a given workorder.

SELECT worktodo.worker, COUNT(worktodo.workorder) AS TotalWorkToDo,
    SUM(CASE WHEN (workdone.workorder IS NULL AND duedate < getdate() )
        OR donedate > duedate
    THEN 1 ELSE 0 END) AS TotalLateWorkToDo
FROM worktodo
LEFT OUTER JOIN 
    (SELECT workorder, min(donedate) AS donedate FROM workdone
        GROUP BY workorder) as workdone
    ON worktodo.workorder = workdone.workorder
GROUP BY worktodo.worker

Upvotes: 1

Related Questions