select t1.*,t2.start_date,t2.status from(
select e.user_id,e.emp_name,o.name,count(*) from t_app_work_extra t
left join m_employee e on t.proposer=e.user_id left join m_organization o on e.dept_id=o.org_id
where to_char(t.end_date,'HH24')>=21 andto_char(t.start_date,'yyyy-MM-dd')>='2011-11-26' and to_char(t.end_date,'yyyy-MM-dd')<='2012-01-25' group by e.user_id,e.emp_name,o.name) t1
left join(
select e.user_id,t.start_date,decode(t.status,'C','审批通过','P','审批中','N','未提交','D','审批未通过')as status,
count(*) from t_app_work_extra t left join m_employee e on t.proposer=e.user_id left join m_organization o on e.dept_id=o.org_id
where to_char(t.end_date,'HH24')>=21 and to_char(t.start_date,'yyyy-MM-dd')>='2011-11-26' and to_char(t.end_date,'yyyy-MM-dd')<='2012-01-25' group by e.user_id,t.start_date,t.status
) t2 on t1.user_id=t2.user_id
SELECT count(*),
E.EMP_NAME,
M.name
from M_EMPLOYEE E
left join M_ORGANIZATION M on M.ORG_ID =E.DEPT_ID
left join T_ECM_BIRTH_BLESS t on T.TO_USER=E.USER_ID
WHERE TO_CHAR(E.BIRTHDAY,'MM')>=TO_CHAR(sysdate,'MM')group by E.EMP_NAME