1.统计部门员工的学历
SELECT A01.Dept_Code,
SUM(CASE WHEN isnull(A01107,0) in (‘18‘) THEN 1 ELSE 0 END) AS "本科（含以上）",
SUM(CASE WHEN isnull(A01107,0) in (‘16‘) THEN 1 ELSE 0 END) AS "大专",
SUM(CASE WHEN isnull(A01107,0) < ‘16‘ THEN 1 ELSE 0 END) AS "大专及以下"
FROM A01 WHERE (A0191=‘1‘ AND A01.Dept_Code = :部门) GROUP BY A01.Dept_Code
2.统计部门员工的司龄
SELECT DISTINCT A01.Dept_Code,
SUM(CASE WHEN isnull(A0152,0) <=0.5 THEN 1 ELSE 0 END) AS "半年以下",
SUM(CASE WHEN isnull(A0152,0) >0.5 and isnull(A0152,0)<=1 THEN 1 ELSE 0 END) AS "半年以上一年以下",
SUM(CASE WHEN isnull(A0152,0) >1 and isnull(A0152,0)<3 THEN 1 ELSE 0 END) AS "一年以上三年以下",
SUM(CASE WHEN isnull(A0152,0) >=3 THEN 1 ELSE 0 END) AS "三年(含)以上" FROM A01 WHERE (A0191=‘1‘ AND A01.DEPT_CODE = :部门) GROUP BY A01.DEPT_CODE
3.统计部门员工的年龄
SELECT DISTINCT A01.Dept_Code,
SUM(CASE WHEN isnull(A01101,0) <30 THEN 1 ELSE 0 END) AS "30周岁以下",
SUM(CASE WHEN isnull(A01101,0) >30 and isnull(A01101,0)<=40 THEN 1 ELSE 0 END) AS "31周岁-40周岁",
SUM(CASE WHEN isnull(A01101,0) >40 and isnull(A01101,0)<=50 THEN 1 ELSE 0 END) AS "41周岁-50周岁",
SUM(CASE WHEN isnull(A01101,0) >50 THEN 1 ELSE 0 END) AS "51周岁以上" FROM A01 WHERE (A0191=‘1‘ AND A01.DEPT_CODE = :部门) GROUP BY A01.DEPT_CODE
4.员工年休假的计算
UPDATE A01 SET A0152=ROUND(datediff(mm,A0141,GETDATE())/12,0) WHERE A0191=‘1‘ AND A0141 IS NOT NULL and day(A0141)<=15 //司龄的计算，小于15号入职
UPDATE A01 SET A0152=ROUND((datediff(mm,A0141,GETDATE())-1)/12,0) WHERE A0191=‘1‘ AND A0141 IS NOT NULL and day(A0141)>15 //大于15号入职