locked
Query for event time diff RRS feed

  • Question

  • HI,

    I have following set of data.

      UID  eventtime      from        to 

        1     tim          NULL          stage1

        1     tim         stage1         stage2

        1     tim         stage2         stage3

        1     tim         stage3         stage4

        2  tim         NULL            stage1

        2     tim  stage1    stage2

        2  tim           stage2       stage4        

        3     tim  NULL   stage1    

        3     tim         stage1        stage2

        3     tim  stage2  stage3    

        3     tim         stage3        stage4

     

        we need the  event time diff in HH:MM:SS format  when the event changes from (stage2 to stage3) or (stage2 to stage4)

    Please advice as this is critical for one of my requirement.

     

    Thanks in advance.

     

     

    Thursday, April 22, 2010 6:50 PM

Answers

  • this ?

     

    declare @data table
    (
      UID    int,
      EventTime  datetime,
      [From]    varchar(10),
      [To]    varchar(10)
    )
    
    insert into @data
    select  1, '2010-04-01 01:01', NULL,   'stage1' union all
    select  1, '2010-04-01 02:22', 'stage1', 'stage2' union all
    select  1, '2010-04-01 03:33', 'stage2', 'stage3' union all
    select  1, '2010-04-01 06:11', 'stage3', 'stage4' union all
    select  2, '2010-04-02 02:02', NULL,   'stage1' union all
    select  2, '2010-04-02 04:22', 'stage1', 'stage2' union all
    select  2, '2010-04-02 06:44', 'stage2', 'stage4' union all
    select  3, '2010-04-03 01:01', NULL,   'stage1' union all
    select  3, '2010-04-03 04:23', 'stage1', 'stage2' union all
    select  3, '2010-04-03 07:31', 'stage2', 'stage3' union all
    select  3, '2010-04-03 08:11', 'stage3', 'stage4'
    
    select  d1.UID,
      [FromStage]   = d2.[From],
      [ToStage]  = d2.[To],
      TimeDiff  = convert(varchar(10), dateadd(second, datediff(second, d1.EventTime, d2.EventTime), 0), 108)
    from  @data d1
      inner join @data d2  on  d1.UID  = d2.UID
            and  d1.[To] = d2.[From]
    
    /*
    UID     FromStage ToStage  TimeDiff 
    ----------- ---------- ---------- ----------
    1      stage1   stage2   01:21:00
    1      stage2   stage3   01:11:00
    1      stage3   stage4   02:38:00
    2      stage1   stage2   02:20:00
    2      stage2   stage4   02:22:00
    3      stage1   stage2   03:22:00
    3      stage2   stage3   03:08:00
    3      stage3   stage4   00:40:00
    
    (8 row(s) affected)
    
    */

     


    KH Tan
    • Proposed as answer by Gopi V Friday, April 23, 2010 4:55 AM
    • Marked as answer by KJian_ Wednesday, April 28, 2010 9:20 AM
    Friday, April 23, 2010 2:28 AM

All replies

  • Hi, 

     

    Can you post sample data so that i can make an attempt to help you. I am unable to understand what exactly you are looking to do?

    Thursday, April 22, 2010 7:08 PM
  • What is with this data?
    Thursday, April 22, 2010 7:09 PM
  • UID  Event Time      From Stage To stage

    1 4/17/10 1:32 AM      NULL Stage1 
    1 4/17/10 1:32 AM        Stage1 Stage2
    1 4/17/10 1:33 AM        Stage2 Stage3
    1 4/18/10 12:04 AM Stage3 Stage4
    2 4/18/10 12:11 AM NULL Stage1 
    2 4/18/10 12:11 AM Stage1 Stage2
    2 4/18/10 12:12 AM Stage2 Stage4
    3 4/18/10 12:11 AM NULL Stage1 
    3 4/18/10 12:11 AM Stage1 Stage2
    3 4/18/10 12:17 AM Stage2 Stage3
    3 4/18/10 12:17 AM Stage3 Stage4

    This talks about various events of a item. Which goes from NULL to stage1, stage 1 to 2 then from 2 to 3, from 3 to 4. In b/w stage can be null to stage1, stage1 to stage2 and then stage2 to stage 4.

    we need the event time diff when the event changes from (stage2 to stage3) or (stage2 to stage4)

    Please help asap. Thanks
    Thursday, April 22, 2010 7:36 PM
  • Sari,

    The stage change is happening in three ways. One is between FromStage and ToStage in the same row, one in FromStage in different rows, and one in ToStage in different rows. Which one you want to track?

    Can you post your desired output?


    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, April 22, 2010 7:43 PM
  •    UID  eventtime(dattime)      FROM STAGE        TO Stage 
        1     tim           NULL               stage1
        1     tim               stage1       stage2
        1     tim              stage2                   stage3
        1     tim             stage3                       stage4
        2  tim             NULL                        stage1
        2     tim     stage1               stage2
        2  tim           stage2                     stage4        
        3     tim     NULL             stage1    
        3     tim           stage1              stage2
        3     tim  stage2         stage3    
        3     tim           stage3          stage4

    This talks about various events of a item. Which goes from stage NULL to stage1, stage 1 to 2 ,from 2 to 3 and finally from 3 to 4.
    In b/w stage can go from null to stage1, stage1 to stage2 and then stage2 to stage 4.(skipping stage 2 to stage3)

    we need the event time diff when the event changes from (stage2 to stage3) or (stage2 to stage4)

    Thursday, April 22, 2010 8:00 PM
  • It would be helpful if you post the desired output if the following is not what you need.

    DECLARE @t TABLE(ID INT,eventtime DATETIME,Fromm VARCHAR(20),Too VARCHAR(20))
    INSERT INTO @t
    SELECT 1,GETDATE(),NULL,'stage1' UNION ALL
    SELECT 1,GETDATE()+1,'stage1','stage2' UNION ALL
    SELECT 1,GETDATE()+2,'stage2','stage3' UNION ALL
    SELECT 1,GETDATE()+3,'stage3','stage4' UNION ALL
    SELECT 2,GETDATE()+4,NULL, 'stage1' UNION ALL
    SELECT 2,GETDATE()+5,'stage1','stage2' UNION ALL
    SELECT 2,GETDATE()+6,'stage2','stage4' UNION ALL
    SELECT 3,GETDATE()+7,NULL,'stage1' UNION ALL
    SELECT 3,GETDATE()+8,'stage1','stage2' UNION ALL
    SELECT 3,GETDATE()+9,'stage2','stage3' UNION ALL
    SELECT 3,GETDATE()+10,'stage3','stage4'
    
    
    
    SELECT *
    FROM @t
    WHERE (Fromm = 'stage2' AND Too = 'Stage3')
    	OR(Fromm = 'stage2' AND Too = 'Stage4')
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    Thursday, April 22, 2010 8:18 PM
  • HI 

    I need time diff for a all IDS like  when ID1 reahces from stage2 to stage3 or it reached from stage2 to stage4.

    Thursday, April 22, 2010 8:41 PM
  • this ?

     

    declare @data table
    (
      UID    int,
      EventTime  datetime,
      [From]    varchar(10),
      [To]    varchar(10)
    )
    
    insert into @data
    select  1, '2010-04-01 01:01', NULL,   'stage1' union all
    select  1, '2010-04-01 02:22', 'stage1', 'stage2' union all
    select  1, '2010-04-01 03:33', 'stage2', 'stage3' union all
    select  1, '2010-04-01 06:11', 'stage3', 'stage4' union all
    select  2, '2010-04-02 02:02', NULL,   'stage1' union all
    select  2, '2010-04-02 04:22', 'stage1', 'stage2' union all
    select  2, '2010-04-02 06:44', 'stage2', 'stage4' union all
    select  3, '2010-04-03 01:01', NULL,   'stage1' union all
    select  3, '2010-04-03 04:23', 'stage1', 'stage2' union all
    select  3, '2010-04-03 07:31', 'stage2', 'stage3' union all
    select  3, '2010-04-03 08:11', 'stage3', 'stage4'
    
    select  d1.UID,
      [FromStage]   = d2.[From],
      [ToStage]  = d2.[To],
      TimeDiff  = convert(varchar(10), dateadd(second, datediff(second, d1.EventTime, d2.EventTime), 0), 108)
    from  @data d1
      inner join @data d2  on  d1.UID  = d2.UID
            and  d1.[To] = d2.[From]
    
    /*
    UID     FromStage ToStage  TimeDiff 
    ----------- ---------- ---------- ----------
    1      stage1   stage2   01:21:00
    1      stage2   stage3   01:11:00
    1      stage3   stage4   02:38:00
    2      stage1   stage2   02:20:00
    2      stage2   stage4   02:22:00
    3      stage1   stage2   03:22:00
    3      stage2   stage3   03:08:00
    3      stage3   stage4   00:40:00
    
    (8 row(s) affected)
    
    */

     


    KH Tan
    • Proposed as answer by Gopi V Friday, April 23, 2010 4:55 AM
    • Marked as answer by KJian_ Wednesday, April 28, 2010 9:20 AM
    Friday, April 23, 2010 2:28 AM