declare @tb1 table (Id int identity(1,1), Name nvarchar(50), CheckInCheckOut bit, CheckInCheckOutDateTime datetime)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)Values(‘Poter, Harry’,1,’2011-08-30 17:00:15.090′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:20.140′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-30 17:00:24.890′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-30 17:00:29.983′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-08-31 17:00:38.983′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-08-31 17:00:44.967′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-09-01 17:00:54.950′)
insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,0,’2011-09-01 17:01:03.590′)
–insert into @tb1(Name,CheckInCheckOut,CheckInCheckOutDateTime)values(‘Poter, Harry’,1,’2011-09-07 15:07:22.140′)
–select * from @tb1 order by CheckInCheckOutDateTime asc
declare @tb2 table (Id int, Name nvarchar(50), CheckInCheckOut bit, ClockIn datetime, ClockOut datetime, lastadd bit)
Declare @cnt int
set @cnt = 0;
while (@cnt< (select count(*) from @tb1))
Begin
if((select id from @tb1 where Id=@cnt+1)=1)
Begin
if((select CheckInCheckOut from @tb1 where Id=@cnt+1)=1)
Begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=1)
End
Else
Begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,null,CheckInCheckOutDateTime,CheckInCheckOut from @tb1 where Id=1)
End
End
else
Begin
if((select top 1 lastadd from @tb2 order by Id desc)(select CheckInCheckOut from @tb1 where Id=@cnt+1))
Begin
if((select top 1 lastadd from @tb2 order by Id desc)=1)
Begin
update @tb2
set ClockOut = (select CheckInCheckOutDateTime from @tb1 where Id=@cnt+1)
,lastadd = (select CheckInCheckOut from @tb1 where Id=@cnt+1)
where Id = (select top 1 id from @tb2 order by Id desc)
end
else
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
End
else
begin
if((select CheckInCheckOut from @tb1 where Id=@cnt+1)=1)
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,CheckInCheckOutDateTime,null,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
else
begin
Insert into @tb2(Id,Name,CheckInCheckOut,ClockIn,ClockOut,lastadd)
(select id,Name,CheckInCheckOut,null,CheckInCheckOutDateTime,CheckInCheckOut from @tb1 where Id=@cnt+1)
end
end
End
set @cnt=@cnt+1
End
select * from @tb2
