select f.*, g.trueworkload from (select a.username, d.batchcode, c.workflowname, count(*) workload from t_systemuserinfo a, t_genworkflow c, t_batch d, t_companyinfo e, (select distinct batchid, companyid, userid, direction, fromwkfid, towkfid from dbo.T_WorkflowLog where dotime between '2008-09-02' and '2008-09-03') as b where a.systemuserid = b.userid and b.fromwkfid = c.genworkflowid and b.batchid = d.batchid and b.companyid = e.companyid group by a.username, c.workflowname, d.batchcode) f, (select a.username, d.batchcode, c.workflowname, count(*) trueworkload from t_systemuserinfo a, t_genworkflow c, t_batch d, t_companyinfo e, (select distinct batchid, companyid, userid, direction, fromwkfid, towkfid from dbo.T_WorkflowLog where dotime between '2008-09-02' and '2008-09-03') as b where a.systemuserid = b.userid and b.fromwkfid = c.genworkflowid and b.batchid = d.batchid and b.companyid = e.companyid and e.datastatus = 1 and e.companyid not in (select h.companyid from t_companyinfotmp h where h.batchid = b.batchid) group by a.username, c.workflowname, d.batchcode, e.datastatus) g where f.batchcode = g.batchcode and f.workflowname = g.workflowname and f.username = g.username union select f.*, g.trueworkload from (select a.username, d.batchcode, c.workflowname, count(*) workload from t_systemuserinfo a, t_genworkflow c, t_batch d, t_companyinfotmp e, (select distinct batchid, companyid, userid, direction, fromwkfid, towkfid from dbo.T_WorkflowLog where dotime between '2008-09-02' and '2008-09-03') as b where a.systemuserid = b.userid and b.fromwkfid = c.genworkflowid and b.batchid = d.batchid and b.companyid = e.companyid and b.batchid = e.batchid group by a.username, c.workflowname, d.batchcode) f, (select a.username, d.batchcode, c.workflowname, count(*) trueworkload from t_systemuserinfo a, t_genworkflow c, t_batch d, t_companyinfotmp e, (select distinct batchid, companyid, userid, direction, fromwkfid, towkfid from dbo.T_WorkflowLog where dotime between '2008-09-02' and '2008-09-03') as b where a.systemuserid = b.userid and b.fromwkfid = c.genworkflowid and b.batchid = d.batchid and b.companyid = e.companyid and e.datastatus = 1 and b.batchid = e.batchid group by a.username, c.workflowname, d.batchcode, e.datastatus) g where f.batchcode = g.batchcode and f.workflowname = g.workflowname and f.username = g.username compute sum(workload), sum(trueworkload)