我有一个分析表,其中包含每个用户每天的四条记录。我想要一份月报显示如下时间:
UserID [1] [2] [3] [4]......... [End of each month]
--------------------------------------------------------------------
1 7:00 7:40 7:20 N/A......... Absence
1 12:00 13:00 12:45 12:50....... Absence
1 14:00 14:10 13:45 N/A......... Absence
1 17:30 17:30 17:45 13:10....... Absence
以下是我的记录:
所以,我开始做了几个星期的研究,最后创建了动态sql脚本来创建pivot表。我的sql的第一部分是分析来自我的指纹设备的数据和每个员工的工作时间。分析完数据后,我将其插入一个名为hrms.AnalyzedRecord的表中,该表包含来自每个用户设备的所有干净记录。脚本编写如下:
USE [KCLDB]
GO
/****** Object: StoredProcedure [hrms].[GetMonthlyAttedance] Script Date: 7/27/18 10:21:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [hrms].[GetMonthlyAttedance] (@date DATE = '',
@location NVARCHAR(MAX) = '',
@sortby SMALLINT = 0)
AS
BEGIN
DECLARE @from_first_time_in TIME(7),
@to_first_time_in TIME(7),
@from_first_time_out TIME(7),
@to_first_time_out TIME(7),
@from_second_time_in TIME(7),
@to_second_time_in TIME(7),
@from_second_time_out TIME(7),
@to_second_time_out TIME(7),
@counter INT = 0,
@first_time_in TIME(7),
@first_time_out TIME(7),
@second_time_in TIME(7),
@second_time_out TIME(7),
@duty_time_in TIME (7),
@duty_time_out TIME(7),
@result_time INT,
@hour INT,
@minute INT,
@second INT,
@status NVARCHAR(30),
@employee_count INT,
@date_range INT,
@date_step INT,
@built_date date
SELECT @date_range = Datediff(day, @date, Dateadd(MONTH, 1, @date));
print('This is date_range:' + cast(@date_range as nvarchar(max)))
SELECT @date_step = 1;
SELECT @employee_count = Count(EmployeeID)
FROM hrms.Employee
--DECLARE @temptable TABLE
-- (
-- ID INT,
-- DN INT,
-- UserID NVARCHAR(10),
-- Clock TIME(7),
-- [Date] DATE,
-- [Action] NVARCHAR(max),
-- VerifyMode NVARCHAR(max),
-- [Status] NVARCHAR(30)
-- )
WHILE @date_step <= @date_range
BEGIN
WHILE @counter < @employee_count
BEGIN
/*** Getting time to scan of each employee ***/
SELECT @built_date = Cast(Year(@date) AS NVARCHAR(max)) + '-'
+ Cast(Month(@date) AS NVARCHAR(MAX)) + '-'
+ Cast(@date_step AS NVARCHAR(MAX))
SELECT @from_first_time_in = FromTimeIn
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'AM'
SELECT @to_first_time_in = ToTimeIn
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'AM'
SELECT @from_first_time_out = FromTimeOut
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'AM'
SELECT @to_first_time_out = ToTimeOut
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'AM'
SELECT @from_second_time_in = FromTimeIn
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'PM'
SELECT @to_second_time_in = ToTimeIn
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'PM'
SELECT @from_second_time_out = FromTimeOut
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'PM'
SELECT @to_second_time_out = ToTimeOut
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'PM' ---End getting duty time
/***
- Getting time log and duty time of each employee
- Calculating the result and status between duty time and time log of each employee
- Save the calculation result
***/
--- Getting time log and duty time [first time in]
SELECT @first_time_in = (SELECT TOP 1 Clock
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN
@from_first_time_in
AND
@to_first_time_in
AND UserID = Cast(FORMAT(@counter,
'00000'
)
AS
NVARCHAR
(10))
ORDER BY ID ASC)
PRINT('This is the date: ' + cast(@built_date as nvarchar(max)))
SELECT @duty_time_in = D.BeginTime
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'AM'
--- Calculation result and status [first time in]
SELECT @result_time = Datediff (SECOND, @duty_time_in,
â@first_time_in)
SELECT @hour = @result_time / 3600
SELECT @minute = ( @result_time % 3600 ) / 60
SELECT @second = ( @result_time % 3600 ) % 60
SELECT @status = CASE
WHEN ( @first_time_in > @duty_time_in ) THEN (
N'á
á¼áááºá('
+ Cast(FORMAT(@hour, '00') AS NVARCHAR) + ':'
+ Cast(FORMAT(@minute, '00') AS NVARCHAR)
+ ':'
+ Cast(FORMAT(@second, '00') AS NVARCHAR)
+ N' áá·áá¶áá¸)' )
ELSE ''
END
--- Saving result
INSERT INTO hrms.AnalyzedRecord
SELECT TOP 1 ID,
DN,
UserID,
Clock,
[Date],
'In',
VerifyMode,
@status
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN @from_first_time_in AND @to_first_time_in
AND UserID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10))
ORDER BY ID ASC --- saving [1]
--- Getting time log and duty time [first time out]
SELECT @first_time_out = (SELECT TOP 1 Clock
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN
@from_first_time_out
AND
@to_first_time_out
AND UserID = Cast(FORMAT(@counter,
'00000')
AS
NVARCHAR(10))
ORDER BY ID DESC)
SELECT @duty_time_out = D.EndTime
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10)
)
AND D.Remark = 'AM' ---end getting log and duty time
--- Calculation result and status [first time out]
SELECT @result_time = Datediff (SECOND, â@first_time_out,
@duty_time_out
)
SELECT @hour = @result_time / 3600
SELECT @minute = ( @result_time % 3600 ) / 60
SELECT @second = ( @result_time % 3600 ) % 60
SELECT @status = CASE
WHEN ( @first_time_out < @duty_time_out ) THEN (
N'á
áááá»á('
+ Cast(FORMAT(@hour, '00') AS NVARCHAR) + ':'
+ Cast(FORMAT(@minute, '00') AS NVARCHAR)
+ ':'
+ Cast(FORMAT(@second, '00') AS NVARCHAR)
+ N' áá·áá¶áá¸)' )
ELSE ''
END --- end calcuation
--- Saving result
INSERT INTO hrms.AnalyzedRecord
SELECT TOP 1 ID,
DN,
UserID,
Clock,
[Date],
'Out',
VerifyMode,
@status
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN @from_first_time_out AND @to_first_time_out
AND UserID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10))
ORDER BY ID DESC --- end saving [2]
--- Getting time log and duty time [second time in]
SELECT @second_time_in = (SELECT TOP 1 Clock
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN
@from_second_time_in
AND
@to_second_time_in
AND UserID = Cast(FORMAT(@counter,
'00000')
AS
NVARCHAR(10))
ORDER BY ID ASC)
SELECT @duty_time_in = (SELECT D.BeginTime
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(
FORMAT(@counter, '00000')
AS
NVARCHAR(10))
AND D.Remark = 'PM')
---end getting log and duty time
--- Calculation result and status [second time in]
SELECT @result_time = Datediff (SECOND, @duty_time_in,
â@second_time_in)
SELECT @hour = @result_time / 3600
SELECT @minute = ( @result_time % 3600 ) / 60
SELECT @second = ( @result_time % 3600 ) % 60
SELECT @status = CASE
WHEN ( @second_time_in > @duty_time_in ) THEN (
N'á
á¼áááºá('
+ Cast(FORMAT(@hour, '00') AS NVARCHAR) + ':'
+ Cast(FORMAT(@minute, '00') AS NVARCHAR)
+ ':'
+ Cast(FORMAT(@second, '00') AS NVARCHAR)
+ N' áá·áá¶áá¸)' )
ELSE ''
END ---end calculation
--- Saving Result
INSERT INTO hrms.AnalyzedRecord
SELECT TOP 1 ID,
DN,
UserID,
Clock,
[Date],
'In',
VerifyMode,
@status
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN @from_second_time_in AND @to_second_time_in
AND UserID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10))
ORDER BY ID ASC ---end saving [3]
--- Getting time log and duty time [second time out]
SELECT @second_time_out = (SELECT TOP 1 Clock
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN
@from_second_time_out
AND
@to_second_time_out
AND UserID = Cast(FORMAT(@counter,
'00000'
) AS
NVARCHAR(10))
ORDER BY ID DESC)
SELECT @duty_time_out = (SELECT D.EndTime
FROM hrms.DutyTime AS D
JOIN hrms.[GroupDutyTime] AS GD
ON GD.DutyTimeID = D.DutyTimeID
JOIN hrms.[Group] AS G
ON G.GroupID = GD.GroupID
JOIN hrms.Employee AS E
ON E.GroupID = G.GroupID
WHERE E.EmployeeID = Cast(
FORMAT(@counter, '00000'
) AS
NVARCHAR(10))
AND D.Remark = 'PM')
---end getting log and duty time
--- Calculation result and status [second time out]
SELECT @result_time = Datediff (SECOND, @second_time_out,
â@duty_time_out)
SELECT @hour = @result_time / 3600
SELECT @minute = ( @result_time % 3600 ) / 60
SELECT @second = ( @result_time % 3600 ) % 60
SELECT @status = CASE
WHEN ( @second_time_out < @duty_time_out ) THEN (
N'á
áááá»á('
+ Cast(FORMAT(@hour, '00') AS NVARCHAR) + ':'
+ Cast(FORMAT(@minute, '00') AS NVARCHAR)
+ ':'
+ Cast(FORMAT(@second, '00') AS NVARCHAR)
+ N' áá·áá¶áá¸)' )
ELSE ''
END ---end calculation
--- Saving Result
INSERT INTO hrms.AnalyzedRecord
SELECT TOP 1 ID,
DN,
UserID,
Clock,
[Date],
'Out',
VerifyMode,
@status
FROM hrms.AttendanceRecord
WHERE [Date] = @built_date
AND Clock BETWEEN @from_second_time_out AND
@to_second_time_out
AND UserID = Cast(FORMAT(@counter, '00000') AS NVARCHAR(10))
ORDER BY ID DESC ---end saving [4]
SET @counter = @counter + 1
END
SET @date_step += 1
SET @counter = 0
END
PRINT('Looping Date: ' + cast(@date_step as nvarchar(max)))
/*** Generating dynamic script for monthly report ***/
DECLARE @sql NVARCHAR(MAX),
@sql_start NVARCHAR(MAX),
@sql_end NVARCHAR(MAX)
SELECT @sql = 'SELECT DISTINCT UserID '
SELECT @date_range = Datediff(day, @date, Dateadd(MONTH, 1, @date));
SELECT @date_step = 1;
SELECT @sql_start = '';
SELECT @sql_end = '';
WHILE @date_step <= @date_range
BEGIN
SELECT @sql_start += ', ISNULL([' + Cast(Year(@date) AS NVARCHAR(max))
+ '-' + Cast(Month(@date) AS NVARCHAR(MAX))
+ '-' + Cast(@date_step AS NVARCHAR(MAX)) + '], '''')'
+ 'AS ' + '['
+ Cast(Year(@date) AS NVARCHAR(max)) + '-'
+ Cast(Month(@date) AS NVARCHAR(MAX)) + '-'
+ Cast(@date_step AS NVARCHAR(MAX)) + ']';
SELECT @sql_end += '[' + Cast(Year(@date) AS NVARCHAR(max))
+ '-' + Cast(Month(@date) AS NVARCHAR(MAX))
+ '-' + Cast(@date_step AS NVARCHAR(MAX))
+ '], ';
SET @date_step +=1;
END
SELECT @sql += @sql_start
+ 'FROM hrms.AnalyzedRecord PIVOT (MIN([Clock]) FOR [Date] IN('
+ Substring(@sql_end, 1, (Len(@sql_end)-1))
+ ')) AS P'
exec( @sql );
END
truncate table hrms.AnalyzedRecord
declare @date_var date, @location_var nvarchar(max)
select @date_var = DATEADD(month, -1, getdate())
select @location_var = 'KCL'
exec [hrms].[GetMonthlyAttedance] @date_var, @location_var
此代码的结果是:
我很期待看到你的推荐。谢谢。
