先来说说具体的问题吧。

系统中有一张IM的登录记录表,里面是员工IM的登录时间,有登录时间,离线时间,最后活动时间。

 

因为员工上半时有可能会同时登录多个IM,其中的时间会有交集,因为我要计算员工每天IM在线的工作时间,交集时间需要过滤掉。

早上因为思路错误,白白浪费了一个早上的时间,其实直接在sql server中就可以比较方便的计算出相关内容。具体的直接上sql代码。

 

 

--方便演示,定义一个Source的表变量,BeginTime是登录时间,EndTime是离线时间
declare @Source table(ID INT IDENTITY(1,1),BeginTime datetime, EndTime datetime)
--定义输出结果表变量
declare @Result table(id INT IDENTITY(1,1),BeginTime datetime, EndTime DATETIME,OnlineTime INT NULL)

--插入演示数据
insert into @Source values('2010-08-16 08:00:00','2010-08-16 08:40:00')
insert into @Source values('2010-08-16 09:00:00','2010-08-16 11:30:00')
insert into @Source values('2010-08-16 09:01:00','2010-08-16 11:32:00')
insert into @Source values('2010-08-16 10:01:00','2010-08-16 12:01:00')
insert into @Source values('2010-08-16 11:00:00','2010-08-16 12:05:00')
insert into @Source values('2010-08-16 12:10:00','2010-08-16 13:40:00')
insert into @Source values('2010-08-16 13:10:00','2010-08-16 14:15:00')
insert into @Source values('2010-08-16 14:00:00','2010-08-16 16:32:00')
insert into @Source values('2010-08-16 07:20:00','2010-08-16 16:40:00')
insert into @Source values('2010-08-16 17:00:00','2010-08-16 20:32:00')
insert into @Source values('2010-08-16 07:00:00','2010-08-16 07:18:00')
insert into @Source values('2010-08-16 07:30:00','2010-08-16 08:20:00')

--模拟数据库中的数据,插入一些其他时间的数据
insert into @Source values('2010-08-15 08:00:00','2010-08-16 08:40:00')
insert into @Source values('2010-08-17 09:00:00','2010-08-16 11:30:00')
insert into @Source values('2010-08-14 09:01:00','2010-08-16 11:32:00')
insert into @Source values('2010-08-13 10:01:00','2010-08-16 12:01:00')
insert into @Source values('2010-08-17 11:00:00','2010-08-16 12:05:00')
insert into @Source values('2010-08-17 12:10:00','2010-08-16 13:40:00')
insert into @Source values('2010-08-17 13:10:00','2010-08-16 14:15:00')
insert into @Source values('2010-08-17 14:00:00','2010-08-16 16:32:00')
insert into @Source values('2010-08-15 07:20:00','2010-08-16 16:40:00')
insert into @Source values('2010-08-13 17:00:00','2010-08-16 20:32:00')
insert into @Source values('2010-08-12 07:00:00','2010-08-16 07:18:00')
insert into @Source values('2010-08-11 07:30:00','2010-08-16 08:20:00')

--定义需要用到的变量
DECLARE @count INT,@ID INT,@begintime DATETIME,@endtime DATETIME,@Date DATE

--如果需要指定计算的日期,可以设置日期值,否则为null会计算全部的时间
SET @Date = '2010-08-16 07:30:00'

--如果日期不为空,则清除Source中的垃圾数据
IF @Date IS NOT NULL
BEGIN
    
DELETE FROM @Source WHERE BeginTime < @Date OR BeginTime >= DATEADD(DAY,1,@Date)
END

--计算Count,以便进行遍历
SELECT @count = COUNT(1FROM @Source

--遍历Source中的数据
WHILE @count > 0
BEGIN
    
    
--根据时间排序,每次取1条数据
    SELECT TOP 1 @begintime = BeginTime,@endtime = EndTime ,@ID = ID FROM @Source
    
ORDER BY BeginTime
    
    
--如果Result中没有记录,则直接插入,否则进行判断
    IF NOT EXISTS(SELECT 1 FROM @Result)
    
BEGIN
        
insert into @Result values(@begintime,@endtime,NULL);
    
END
    
ELSE
    
BEGIN
        
--由于Source的数据是经过排序的,所以下面的插入已经无效,直接注释掉
        --IF NOT EXISTS(SELECT 1 FROM @Result WHERE BeginTime < @endtime AND EndTime < @begintime)
        --BEGIN
        --    insert into @Result values(@begintime,@endtime,NULL);
        --END
        
        
--如果开始时间大于Result中的已存在的记录结束时间,则直接插入数据
        IF NOT EXISTS(SELECT 1 FROM @Result WHERE EndTime > @begintime)
        
BEGIN
            
insert into @Result values(@begintime,@endtime,NULL);
        
END
        
--判断Result中的数据开始时间是否大于Source中的开始时间,大于则更新
        ELSE IF EXISTS(SELECT 1 FROM @Result WHERE BeginTime > @begintime AND EndTime < @endtime AND EndTime > @endtime)
        
BEGIN
            
UPDATE @Result SET BeginTime = @begintime WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime > @endtime
        
END
        
--判断Result中的数据结束时间是否小于Source中的结束时间,小于则更新
        ELSE IF EXISTS(SELECT 1 FROM @Result WHERE BeginTime < @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime)
        
BEGIN
            
UPDATE @Result SET EndTime = @endtime WHERE BeginTime < @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime
        
END
        
--判断Result中的记录时间是否被Source中的时间被包含,包含则开始与结束都更新
        ELSE IF EXISTS(SELECT 1 FROM @Result WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime)
        
BEGIN
            
SELECT * FROM @Result WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime
            
UPDATE @Result SET BeginTime = @begintime, EndTime = @endtime WHERE BeginTime > @begintime AND BeginTime < @endtime AND EndTime < @endtime AND EndTime > @begintime
        
END
    
END
    
    
--删除已经读取过的记录
    DELETE FROM @Source WHERE id = @ID
    
--更新Count
    SELECT @count = COUNT(1FROM @Source
END

--计算OnlineTime
UPDATE @Result SET OnlineTime = DATEDIFF(mi,BeginTime,EndTime)
--显示结果
SELECT * FROM @Result


最后的结果

 

 

posted on 2010-08-16 17:37  Blood  阅读(1744)  评论(5编辑  收藏  举报