Display the total number of data horizontally according to the same category in a column
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
