Shulou(Shulou.com)06/01 Report--

As shown in the figure below, statistics are made on the number of days of Kj_tx accumulated leave and Kj_nj annual leave in a certain period of time and shown by column.

Select

A.AlEmpID as employee ID

A.alsdate

A.aledate

AlTime= (

Case when a.ALSTime=480 and a.ALETime=1050 then 1

When a.ALSTime=480 and a.ALETime=720 then 0.5

When a.ALSTime=840 and a.ALETime=1050 then 0.5

When a.ALSTime=870 and a.ALETime=1050 then 0.5

Else 0 end)

A.ALFType as leave Category

From kq_askleave a

Where a.AlEmpID=734 and a.alsdate > = '2018-03-01 00 purl 0000'

Select

Aa. Employee ID

Max (d.Dept_lname) as Department

Max (b.Emp_code) as job number

Max (b.Emp_name) as name

Max (b.Emp_zhiweiname) as position

Isnull (sum (case when aa. Leave category = 'Kj_nj' then aa.AlTime+AlDay end), 0) annual leave

Isnull (sum (case when aa. Leave category = 'Kj_sj' then aa.AlTime+AlDay end), 0) personal leave

Isnull (sum (case when aa. Leave category = 'Kj_bj' then aa.AlTime+AlDay end), 0) sick leave

Isnull (sum (case when aa. Leave category = 'Kj_cj' then aa.AlTime+AlDay end), 0) maternity leave

Isnull (sum (case when aa. Leave category = 'Kj_hj' then aa.AlTime+AlDay end), 0) marriage leave

Isnull (sum (case when aa. Leave category = 'Kj_tx' then aa.AlTime+AlDay end), 0) accumulated leave

From

(select

A.AlEmpID as employee ID

A.alsdate

A.aledate

AlDay= (

Case when a.ALSTime=480 and a.ALETime=1050 then datediff (day,a.alsdate,a.aledate) + 1

When a.ALSTime=480 and a.ALETime=720 then datediff (day,a.alsdate,a.aledate)

When a.ALSTime=840 and a.ALETime=1050 then datediff (day,a.alsdate,a.aledate)

When a.ALSTime=870 and a.ALETime=1050 then datediff (day,a.alsdate,a.aledate)

Else 0 end)

AlTime= (

Case when a.ALSTime=480 and a.ALETime=1050 then 0

When a.ALSTime=480 and a.ALETime=720 then 0.5

When a.ALSTime=840 and a.ALETime=1050 then 0.5

When a.ALSTime=870 and a.ALETime=1050 then 0.5

Else 0 end)

A.ALFType as leave Category

From kq_askleave a

Where a.AlEmpID=734 and a.alsdate > = '2016-11-01 00 and a.aledate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.