CREATE PROCEDURE [dbo].[Proc_UPSState_Statistics] 
     @Guid VARCHAR(32), 
     @State VARCHAR(2), 
     @STime DATETIME, 
     @ETime DATETIME, 
     @R_C INT OUTPUT, 
     @R_S INT OUTPUT 
    
AS 
BEGIN     
    SET NOCOUNT ON; 
    SELECT IDENTITY(INT,1,1) AS ID,Guid,UPSCTState,RecordTime INTO #TSTE FROM [dbo].[EUPSDeviceRunRecordLog] WHERE  Guid = @Guid AND RecordTime >= @STime AND RecordTime <= @ETime  ORDER BY RecordTime 
    DECLARE @R_Count INT,@RS INT,@State_Count INT  --记录总数，累计时长，累计次数 
    SELECT @RS = 0,@State_Count = 0, @R_S = 0, @R_C = 0 --给统计变量，输出返回值给初始化为0 
    SELECT @R_Count = COUNT(*) FROM #TSTE     
    PRINT '符合条件记录：' + STR(@R_Count) 
    IF(@R_Count < 1) --少于两条记录不预统计 
        BEGIN 
            --SELECT @State_Count AS Times,@RS AS Count 
            PRINT '在指定时间段内未能查询 ' + @Guid + ' 设备的状态 ' + @State + ' 的记录' 
        END 
    ELSE BEGIN 
        DECLARE @I INT --计数器，步长计数器 
        DECLARE @ST VARCHAR(2)   --临时状态 
        DECLARE @RTime DATETIME,@LTime DATETIME,@DF DATETIME         --当前记录的记录时间、最后一次（上条）此状态记录的时间、时间常量 
        SELECT @DF =  '1979-01-01 0:01:01', @LTime = @DF    
        
        DECLARE @ST_FLAG INT --符合条件状态记录开始，结果标记，用于统计次数 
        SELECT @I = 1, @ST_FLAG = 0

        WHILE(@I <= @R_Count) 
            BEGIN 
                SELECT @ST = UPSCTState,@RTime = RecordTime FROM #TSTE WHERE ID = @I 
                IF(@ST = @State)                    
                    IF(@LTime = @DF) 
                        BEGIN 
                            SET @LTime = @RTime 
                            SET @ST_FLAG = 1 
                        END 
                    ELSE 
                        BEGIN 
                            SET @RS = @RS + DATEDIFF(S,@LTime,@RTime) 
                            SET @LTime = @RTime 
                        END                    
                ELSE 
                    BEGIN 
                        SET @LTime = @DF 
                        IF(@ST_FLAG = 1) 
                            BEGIN 
                                SET @State_Count  = @State_Count + 1 
                                SET @ST_FLAG = 0 
                            END 
                    END 
                SET @I = @I + 1 
            END

         SELECT @R_C = @State_Count,@R_S = @RS 
         --SELECT @State_Count AS Times, @RS AS Count 
    END 
END