使用游标更新Temp表中的记录

Question

我试图创建一个游标，在临时表中插入第一个记录。之后，它应该获取下一条记录，并将employeeId +日期(格式为05/05/2014)与前一条记录employeeId +日期进行比较。如果是相同的，它应该用当前记录的超时数据更新前一个记录的超时数据。否则就插入新行。

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

Answer 1

根据经验，在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)

如果您不熟悉或不熟悉CTE;

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 objectfield 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中最不应该尝试学习的东西时，我确信我的观点会得到回应。这可能会"迫使"你尝试基于集合的方法。