1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
| CREATE TABLE [HRM].[dbo].[TEMP_X](
[Oid] [nvarchar](50) NULL,
[Name] [nvarchar](250) NULL,
[Date] [nvarchar](8) NULL,
[Time] [int] NULL,
[CodeStatus] [int] NULL,
) ON [PRIMARY]
INSERT INTO [HRM].[dbo].[TEMP_X] (Oid,Name,Date,Time)
SELECT a.Oid,FirstName +' '+LastName,date,sum(time)as time from [HRM].[dbo].[Workload] a
inner join [HRM].[dbo].[EMP_Employee] b
on a.oid = b.oid
WHERE codestatus <> '3' and codestatus <> '300' and codestatus <> '-1'
GROUP BY a.oid,FirstName +' '+LastName,date
UPDATE [HRM].[dbo].[TEMP_X]
set CodeStatus = '1'
where CodeStatus is null
INSERT INTO [HRM].[dbo].[TEMP_X] (Oid,Name,Date,Time)
SELECT a.oid,FirstName +' '+LastName,date,sum(time)as time from [HRM].[dbo].[Workload] a
inner join [HRM].[dbo].[EMP_Employee] b
on a.oid = b.oid
WHERE codestatus = '3' or codestatus = '300' or codestatus = '-1'
GROUP BY a.oid,FirstName +' '+LastName,date
UPDATE [HRM].[dbo].[TEMP_X]
SET CodeStatus = '0'
WHERE CodeStatus is null
UPDATE [HRM].[dbo].[TEMP_X]
SET Time =
(
CASE
WHEN Time >= '120000' and CodeStatus = 0 THEN 120000
ELSE Time
END
)
declare c cursor for select distinct (CONVERT(char(10), a.date,112)) from [Auxiliary].[Calendar] a left join [HRM].[dbo].[TEMP_X] b on CONVERT(char(10), a.date,112) = b.date where a.Date >= '20160801' and a.Date <= '20160818'
declare @pivot nvarchar(max), @sum nvarchar(max), @sql nvarchar(max), @sql2 nvarchar(max), @sql3 nvarchar(max), @col nvarchar(max), @A int, @B int , @D nvarchar(max)
select @pivot='', @sum='', @D=''
open c
fetch c into @col
while @@FETCH_STATUS = 0
begin
select
@pivot = @pivot + ' ['+ cast(@col as varchar(30)) +'], ',
@sum = @sum + 'ROUND (SUM (cast(['+ cast (@col as varchar(30)) +']as float))/360000,2) AS ['+ cast(@col as varchar(30)) +'],'
fetch c into @col
end
close c
deallocate c
set @sql = 'SELECT Name, '+ left(@sum, len(@sum)-1) + '
FROM [HRM].[dbo].[TEMP_X]
PIVOT
(SUM (Time)
FOR Date IN ('+ left(@pivot, len(@pivot)-1)+ ')) AS Date
--where Oid = ''KgWVwBwK''
GROUP BY Name'
exec(@sql)
DROP TABLE [HRM].[dbo].[TEMP_X] |
