2012-10-22

SQL Server - Check continued duration

This query helps me find the data which its activity/class/type continued same value for a while, check any abnormal data exist in the table.



USE DTNS
GO

DECLARE    @Machine_Name    VARCHAR(30),
        @Start_Date        DATETIME,
        @End_Date        DATETIME,
        @Continued_Days INT,
        @Class            VARCHAR(30),
        @Type            VARCHAR(30)
      
SET @Machine_Name = 'KKK_D5'        --'GGG_D4'
SET @Start_Date = '2012-01-01'        --'2008-01-01'
SET @End_Date = '2012-10-01'        --'2009-01-01'
SET @Continued_Days = 4
--Thest two variable used for dynamic column name
SET @Class = 'IBG_class'            --'class', 'IBG_class', 'AMB_class', 'OIR_class', 'NVR_class', 'DBP_class', 'JVN_class', 'TSX_class', 'MRB_class', 'AAA_class', 'GNI_Class'
SET @Type = 'IBG_type'                --'type', 'IBG_type', 'AMB_type', 'OIR_type', 'NVR_type', 'DBP_type', 'JVN_type', 'TSX_type', 'MRB_type', 'AAA_type', 'GNI_Type'

DECLARE @Dates                    DATETIME,
        @A_Start_Date            DATETIME,
        @Old_Start_Date            DATETIME,
        @Old_End_Date            DATETIME,
        @Old_End_Duration        INT,
        @Durations                INT,
        @Start_Duration            INT,
        @Names                    VARCHAR(100),
        @Start_Name                VARCHAR(100),
        @Classes                VARCHAR(30),
        @Start_Class            VARCHAR(30),      
        @Types                     VARCHAR(30),
        @Start_Type                VARCHAR(30),
        @A_Qtr                    VARCHAR(500),
        @Start_Dates            DATETIME,
        @End_Dates                DATETIME      

SET @Dates = ''
SET @Start_Class = ''
SET @Start_Type = ''
SET @Old_Start_Date = ''
--SET @Old_End_Date = ''

DECLARE @Check_Date TABLE  
        (    [Start Date]            DATETIME,
            [Start Date Duration]   INT,
            [End Date]                DATETIME,
            [End Date Duration]        INT,
            Class                    VARCHAR(50),
            [Type]                    VARCHAR(50),
            [Continued Days]        DECIMAL(30,1),
            [Total Hours]            FLOAT )

SET @A_Qtr = 'SELECT start_time, duration, name,  ' + @Class + ', ' + @Type
SET @A_Qtr = @A_Qtr + ' FROM ' + 'DTNS.dbo.A_log al'
SET @A_Qtr = @A_Qtr + ' JOIN DBGNI.dbo.B_A_def bad ON bad.A_id = al.A_id '
SET @A_Qtr = @A_Qtr + ' WHERE al.I_id = dbo.GetIId(''' + @Machine_Name + ''')'
SET @A_Qtr = @A_Qtr + ' AND al.start_time >= ''' + CONVERT(VARCHAR(10), @Start_Date, 120) + ''' AND al.start_time < ''' + CONVERT(VARCHAR(10), @End_Date, 120) + ''''
SET @A_Qtr = @A_Qtr + ' ORDER BY al.start_time'
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--EXEC (@A_Qtr)
EXEC ( 'DECLARE A_Date_Check CURSOR FOR ' + @A_Qtr)

OPEN A_Date_Check
FETCH NEXT FROM A_Date_Check INTO @Dates, @Durations, @Names, @Classes, @Types

WHILE @@FETCH_STATUS = 0
BEGIN
    --If encounter specified class and type, then recalculate continued days  
    IF    ( @Class = 'IBG_class' AND @Classes = 'production')
        OR ( @Classes = 'Operating' AND @Type = 'Time' )
        OR ( @Classes = 'operating' AND @Type = 'facecut' )
        OR ( @Classes = 'Operational' AND @Type = 'Digging' )
    BEGIN
        SET @Start_Class = @Classes
        SET @Start_Type = @Types              
    END          
    ELSE
    BEGIN
        --If class or type are not equal to previous record, or there is a missing gap inside the dataset, then recalculate continued days
        IF @Start_Class <> @Classes OR @Start_Type <> @Types OR (DATEDIFF(DAY, @Old_End_Date, @Dates) > 1)
        BEGIN
            SET @A_Start_Date = @Dates
            SET @Start_Duration = @Durations
            SET @Start_Class = @Classes
            SET @Start_Type = @Types  
          
            --Capture Missing Duration
            IF ((DATEDIFF(SECOND, @Old_End_Date, @Dates) - @Old_End_Duration)/1.0/86400 >= @Continued_Days)
            BEGIN              
                INSERT INTO @Check_Date([Start Date],
                                        [Start Date Duration],
                                        [End Date],
                                        [End Date Duration],
                                        Class,
                                        [Type],
                                        [Continued Days],
                                        [Total Hours] )
                VALUES(    @Old_End_Date,
                        @Old_End_Duration,
                        @Dates,
                        @Durations,
                        'Missing Duration',
                        'Missing Duration',
                        (DATEDIFF(SECOND, @Old_End_Date, @Dates) - @Old_End_Duration)/1.0/86400,
                        (DATEDIFF(SECOND, @Old_End_Date, @Dates) - @Old_End_Duration)/1.0/3600 )
            END
        END
        ELSE
        BEGIN  
            IF (@A_Start_Date <> @Old_Start_Date)
            BEGIN
                IF DATEDIFF(SECOND, @A_Start_Date, @Dates)/1.0/86400 >= @Continued_Days                               
                BEGIN
                    INSERT INTO @Check_Date([Start Date],
                                            [Start Date Duration],
                                            [End Date],
                                            [End Date Duration],
                                            Class,
                                            [Type],
                                            [Continued Days],
                                            [Total Hours] )
                    VALUES(    @A_Start_Date,
                            @Start_Duration,
                            @Dates,
                            @Durations,
                            @Start_Class,
                            @Start_Type,
                            (DATEDIFF(SECOND, @A_Start_Date, @Dates) + @Durations)/1.0/86400,
                            (DATEDIFF(SECOND, @A_Start_Date, @Dates) + @Durations)/1.0/3600 )
                    SET @Old_Start_Date = @A_Start_Date                                  
                END                                                                  
            END
            ELSE
            BEGIN                              
                UPDATE    @Check_Date
                SET        [End Date] = @Dates,
                        [End Date Duration] = @Durations,
                        [Continued Days] = (DATEDIFF(SECOND, @A_Start_Date, @Dates) + @Durations)/1.0/86400,
                        [Total Hours] =    (DATEDIFF(SECOND, @A_Start_Date, @Dates) + @Durations)/1.0/3600
                WHERE    [Start Date] = @A_Start_Date
                --SET    @Old_End_Date = @Dates
                --PRINT @Old_End_Date                                                      
            END
        END          
    END  
    SET @Old_End_Date = @Dates
    SET @Old_End_Duration = @Durations      
    FETCH NEXT FROM A_Date_Check INTO @Dates, @Durations, @Names, @Classes, @Types              
END

CLOSE A_Date_Check
DEALLOCATE A_Date_Check

/*
SELECT    @Machine_Name    [Machine Name],
        @Start_Date        [Start Date],
        @End_Date        [End Date],
        @Continued_Days [Continued Days],
        @Class            [Class],
        @Type            [Type]
*/

SELECT * FROM @Check_Date

IF @@ROWCOUNT > 0
BEGIN
    DECLARE Check_Date_Details CURSOR FOR SELECT [Start Date], [End Date], Class FROM @Check_Date
    OPEN Check_Date_Details
    FETCH NEXT FROM Check_Date_Details INTO @Start_Dates, @End_Dates, @Classes
   
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @Classes <> 'Missing Duration'
        BEGIN
            SELECT    *
            FROM    DTNS.dbo.A_log al
                    JOIN DBGNI.dbo.B_A_def bad
                      ON bad.A_id = al.A_id
            WHERE    al.I_id = dbo.GetIId(@Machine_Name)
                    AND start_time BETWEEN @Start_Dates AND @End_Dates
        END          
      
        FETCH NEXT FROM Check_Date_Details INTO @Start_Dates, @End_Dates, @Classes
    END
    CLOSE Check_Date_Details
    DEALLOCATE Check_Date_Details
END

No comments:

Post a Comment