我正在尝试创建一个游标，该游标将第一条记录插入到临时表中。之后，它应获取下一条记录，并将employeeId + date（格式为05/05/2014）与上一条记录的employeeId + date进行比较。如果相同，则应使用当前记录的超时数据更新前一个记录的超时数据。否则，应插入新行。
I have table in my database with the following columns:
employeeID(int), employeeName varchar(max), Time datetime.
1 , Tim , 05/05/2014 08:15:42
1 , Tim , 05/05/2014 16:30:51
1 , Tim , 06/23/2014 07:00:00
1 , Tim , 06/23/2014 09:00:00
1 , Tim , 06/23/2014 11:00:00
1 , Tim , 06/23/2014 16:30:00
我用以下几列创建了一个临时表，最终结果应类似于以下信息。
employeeID, employeeName , Time in , Time out
1 , Tim , 05/05/2014 08:15:42 , 05/05/2014 16:30:51
1 , Tim , 06/23/2014 07:00:00 06/23/2014 16:30:00
下面是我的代码
Drop Table #temp
go
Create Table #temp
(
userid int,
empname varchar(50),
checkin datetime,
checkout datetime
)
Declare @empid int, @empname varchar(50), @date1 datetime, @strdate varchar(12);
Declare @date2 datetime, @time datetime, @loop int;
Declare Report Cursor
For select e.employeeId,e.name,tp.TimePunch, tp.TimePunch as date2,
CONVERT(VARCHAR(10),TimePunch,110) as stringDate
from EmpTimePunch tp
left join EmploeeInfo e on tp.employeeId = tp.employeeId
where (DATEDIFF(s,'2014-05-01 00:00:00.000',TimePunch) >=0
and DATEDIFF(s,getdate(),TimePunch)<=0)
order by employeeId,TimePunch
Open Report
-- looking at each record
Fetch Next From Report
Into @empid, @empname, @date1, @date2,@strdate
declare @empDate varchar(50);
declare @empDate2 varchar(50);
set @empDate = '';
set @empDate2 = '';
set @loop = 0
--0 equals true; while fetching equal true
while @@FETCH_STATUS = 0
Begin
-- insert first record in temp table
if @loop != 0 and @empDate = @empDate2
insert into #temp values(@empid, @empname, @date1, @date2)
--I want to compare the next record
-- if next record have same employee id and date2(05/05/2014) as prievous record.
--update previous record date2 with the date2 from record 2
set @empDate = cast(@empid as varchar(10))+ @strdate;
fetch next from report
Into @empid, @empname, @date1, @date2,@strdate
End
close Report
Deallocate Report
select * from #temp
根据经验，在SQL中应避免使用迭代逻辑。只要有可能，就应利用语言的声明性-将您的代码表示为基于集合的查询。
我提出类似或类似的解决方案；
;WITH MyData (employeeID, employeeName, [Time])
AS
(
SELECT 1, 'Tim', '05/05/2014 08:15:42' UNION ALL
SELECT 1, 'Tim', '05/05/2014 16:30:51' UNION ALL
SELECT 1, 'Tim', '06/23/2014 07:00:00' UNION ALL
SELECT 1, 'Tim', '06/23/2014 09:00:00' UNION ALL
SELECT 1, 'Tim', '06/23/2014 11:00:00' UNION ALL
SELECT 1, 'Tim', '06/23/2014 16:30:00'
)
SELECT employeeID
,employeeName
,[Time In] = MIN([Time])
,[Time Out] = MAX([Time])
FROM MyData
GROUP BY employeeID, employeeName, CAST([Time] AS DATE)
如果您不熟悉CTEs或不满意s；
CREATE TABLE #MyData
(
employeeID INT,
employeeName VARCHAR(50), -- Please use VARCHAR(MAX) as infrequently as possible
ClockTime DATETIME -- In this example, I've changed the field name from [Time] to ClockTime
) -- It is best to avoide reserved words for object\field naming
INSERT INTO #MyData (employeeID, employeeName, ClockTime)
SELECT 1, 'Tim', '05/05/2014 08:15:42' UNION ALL
SELECT 1, 'Tim', '05/05/2014 16:30:51' UNION ALL
SELECT 1, 'Tim', '06/23/2014 07:00:00' UNION ALL
SELECT 1, 'Tim', '06/23/2014 09:00:00' UNION ALL
SELECT 1, 'Tim', '06/23/2014 11:00:00' UNION ALL
SELECT 1, 'Tim', '06/23/2014 16:30:00'
SELECT employeeID
,employeeName
,[Time In] = MIN(ClockTime)
,[Time Out] = MAX(ClockTime)
FROM #MyData
GROUP BY employeeID, employeeName, CAST(ClockTime AS DATE)
脚注; 我确信当我建议CURSORS您在SQL中学习的最后一件事时，我的观点会得到回应。希望这将“迫使”您尝试基于集合的方法。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句