统计一个部门有多少人

  select b.OrganID,count(a.OrganID)
  from View_HREmployees a,SYSOrgans b
    where a.OrganID=b.OrganID group by b.OrganID  

a 为员工表,b 为机构表    

--统计合同有多少人签

       select  h.IPID,COUNT(s.EMPSYSID)  from 
         HRContract h,SYSEmployees s 
         where  h.EMPSYSID=s.EMPSYSID group by  h.IPID

IPID是合同编号

 h合同表，s 员工表

---考勤汇总 按人按年按月   

select NEWID() REPID,r.ERDate1, t.*,v.EMPName,v.OrganName,v.SchoolName TopName,
      v.SchoolID OrganTopIndex,OrganID,v. EMPSYSID
       from 
    (select  EMPID,
             datepart("YY",TISDate) MYear,
             datepart("MM",TISDate) MMonth,
             (count(case  when TISLastStateAM='1' then TISLastStateAM END)
            +count(case  when TISLastStatePM='1' then TISLastStateAM END))*0.5
               as ZCH,--正常
             (count(case  when TISLastStateAM='2' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='2' then TISLastStatePM END) )*0.5
             as  SHJ,--事假
    ( count(case  when TISLastStateAM='3' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='3' then TISLastStatePM END) )*0.5
            as   BJ,--病假
             (count(case  when TISLastStateAM='4' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='4' then TISLastStatePM END)  )*0.5
            as   NJ,--年假
              ( count(case  when TISLastStateAM='5' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='5' then TISLastStatePM END) )*0.5
            as   HJ,--婚假
              (count(case  when TISLastStateAM='6' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='6' then TISLastStatePM END)  )*0.5
            as   CHJ,--产假
             (count(case  when TISLastStateAM='7' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='7' then TISLastStatePM END) )*0.5
            as   SJ,--丧假
             (count(case  when TISLastStateAM='8' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='8' then TISLastStatePM END)  )*0.5
            as   HLJ,--护理假
              ( count(case  when TISLastStateAM='9' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='9' then TISLastStatePM END)  )*0.5
            as   QTJ,--其他假
               (count(case  when TISLastStateAM='10' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='10' then TISLastStatePM END)  )*0.5
            as   CHD,--迟到
               (count(case  when TISLastStateAM='11' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='11' then TISLastStatePM END)  )*0.5
            as   ZT,--早退
            (count(case  when TISLastStateAM='12' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='12' then TISLastStatePM END))*0.5 
            as   MResetDate,--休息
             (count(case  when TISLastStateAM='13' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='13' then TISLastStatePM END))*0.5 
            as   CHCH,--出差
             (count(case  when TISLastStateAM='14' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='14' then TISLastStatePM END))*0.5 
            as   WCH,--外出
             (count(case  when TISLastStateAM='15' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='15' then TISLastStatePM END) )*0.5
            as   JBAN,--加班
             (count(case  when TISLastStateAM='20' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='20' then TISLastStatePM END))*0.5 
            as   WQ,--忘签
               (count(case  when TISLastStateAM='100' then TISLastStateAM END)
            +   count(case  when TISLastStatePM='100' then TISLastStatePM END))*0.5 
            as   KGONG --旷工
                            from HRTimesheets 
                               group by EMPID ,datepart("YY",TISDate) ,datepart("MM",TISDate) ) t
                                        inner join View_HREmployees v on t.EMPID=v.EMPID
                                        inner join HREmpRest  r on r.EMPSYSID=v.EMPSYSID and r.ERYear=t.MYear