工作笔记

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号入职

5.通过数据库语句修改ehr系统的密码：

UPDATE A01 SET PasswordString = dbo.MD5(/*密码多少*/ + 'd+#8p&nn=o30ke6%-') where   a0190=' '        ，其中注释密码的地方填写密码，a0190后填写账号名