Need help finding stop time

• Gone2mt

  Newbie

  Points: 2

  More actions

  August 18, 2006 at 9:47 am

  #116062

  From the table below,  for each Name, I need to find out how long each was stopped (I.e. the time between a Start event and it's previous Stop event.

  I'd appreciate any help to get me started  - Thanks

  Name	Event	Time
  Name1	Start	8/16/06 8:00 AM
  Name2	Start	8/16/06 8:15 AM
  Name2	Stop	8/17/06 5:30 PM
  Name3	Start	8/14/06 4:00 AM
  Name1	Stop	8/19/06 3:00 PM
  Name3	Stop	8/15/06 2:00 AM
  Name3	Start	8/15/06 2:10 AM
  Name1	Start	8/19/06 3:30 PM
  Name2	Start	8/18/06 2:15 PM

• Darren Welch

  SSC Eights!

  Points: 973

  More actions

  August 18, 2006 at 10:03 am

  #655693

  You could try this...

  DECLARE @start_date AS DATETIME

  DECLARE @stop_date AS DATETIME

  DECLARE @name AS VARCHAR(10)

  SET @name='Name1'

  SELECT @start_date = [time] FROM table1 WHERE name = @name AND event = 'start'

  SELECT @stop_date = [time] FROM table1 WHERE name = @name AND event = 'stop'

  SELECT DATEDIFF(mi, @stop_date, @start_date) -- this will give you the difference in minutes.

  Darren

   

• Darren Welch

  SSC Eights!

  Points: 973

  More actions

  August 18, 2006 at 10:06 am

  #655694

  sorry that should read...

  SELECT @start_date = max([time]) FROM table1 WHERE name = @name AND event = 'start'

  SELECT @stop_date = max([time]) FROM table1 WHERE name = @name AND event = 'stop'

  Darren

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  August 18, 2006 at 10:33 am

  #655702

  --test data

  declare @t table

  (

   tName varchar(20) not null

   ,Event varchar(20) not null

   ,tTime datetime not null)

  insert @t

  select 'Name1', 'Start', convert(datetime, '8/16/06 8:00 AM', 0) union all

  select 'Name2', 'Start', convert(datetime, '8/16/06 8:15 AM', 0) union all

  select 'Name2', 'Stop', convert(datetime, '8/17/06 5:30 PM', 0) union all

  select 'Name3', 'Start', convert(datetime, '8/14/06 4:00 AM', 0) union all

  select 'Name1', 'Stop', convert(datetime, '8/19/06 3:00 PM', 0) union all

  select 'Name3', 'Stop', convert(datetime, '8/15/06 2:00 AM', 0) union all

  select 'Name3', 'Start', convert(datetime, '8/15/06 2:10 AM', 0) union all

  select 'Name1', 'Start', convert(datetime, '8/19/06 3:30 PM', 0) union all

  select 'Name2', 'Start', convert(datetime, '8/18/06 2:15 PM', 0)

  -- results

  select T1.tName

   ,T1.tTime as StartedTime

   ,case when T2.tName is null

   then 'UnKnown'

   else cast(datediff(minute, T2.tTime, T1.tTime) as varchar(20))

   end as MinutesStopped

  from @t T1

   left join @t T2 on T1.tName = T2.tName

    and T2.Event = 'Stop'

    and T2.tTime < T1.tTime

  where T1.Event = 'Start'

   and (T2.tTime =

    (select max(T3.tTime)

    from @t T3

    where T3.tName = T1.tName

     and T3.Event = 'Stop'

     and T3.tTime < T1.tTime)

    or T2.tTime is null)

  order by T1.tTime

• PW-201837

  SSC-Insane

  Points: 20805

  More actions

  August 18, 2006 at 10:34 am

  #655703

  -- Table for sample data

  Create Table #Test

  (

    Name varchar(20),

    Event  varchar(20),

    Time  datetime

  )

  -- Populate with sample data

  Insert Into #Test

  Select 'Name1', 'Start',  '8/16/06 8:00 AM' Union All

  Select 'Name2', 'Start', '8/16/06 8:15 AM' Union All

  Select 'Name2', 'Stop', '8/17/06 5:30 PM' Union All

  Select 'Name3', 'Start', '8/14/06 4:00 AM' Union All

  Select 'Name1', 'Stop', '8/19/06 3:00 PM' Union All

  Select 'Name3', 'Stop', '8/15/06 2:00 AM' Union All

  Select 'Name3', 'Start', '8/15/06 2:10 AM' Union All

  Select 'Name1', 'Start', '8/19/06 3:30 PM' Union All

  Select 'Name2', 'Start', '8/18/06 2:15 PM'

  -- Get each start event and time since previous stop.

  -- Returns NULL where there is no previous stop

  Select *, DateDiff(mi, PreviousStop, StartedAt) As MinutesSinceLastStop

  From

  (

    Select t1.Name, t1.Event, t1.Time As StartedAt,

      (Select Max(t2.Time)

       From #Test As t2

       Where t1.Name = t2.Name

       And t2.Event = 'Stop'

       And t2.Time < t1.Time)  As PreviousStop

    From #Test As t1

    Where t1.Event = 'Start'

  ) dt

  Order By Name, StartedAt

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  August 18, 2006 at 10:57 am

  #655713

  PW's approach is more efficient.

  --test data

  declare @t table

  (

   tName varchar(20) not null

   ,Event varchar(20) not null

   ,tTime datetime not null

  )

  -- 7.34% of cost

  insert @t

  select 'Name1', 'Start', convert(datetime, '8/16/06 8:00 AM', 0) union all

  select 'Name2', 'Start', convert(datetime, '8/16/06 8:15 AM', 0) union all

  select 'Name2', 'Stop', convert(datetime, '8/17/06 5:30 PM', 0) union all

  select 'Name3', 'Start', convert(datetime, '8/14/06 4:00 AM', 0) union all

  select 'Name1', 'Stop', convert(datetime, '8/19/06 3:00 PM', 0) union all

  select 'Name3', 'Stop', convert(datetime, '8/15/06 2:00 AM', 0) union all

  select 'Name3', 'Start', convert(datetime, '8/15/06 2:10 AM', 0) union all

  select 'Name1', 'Start', convert(datetime, '8/19/06 3:30 PM', 0) union all

  select 'Name2', 'Start', convert(datetime, '8/18/06 2:15 PM', 0)

  -- results

  -- My approach

  -- 54.64% of cost

  select T1.tName

   ,T1.tTime as StartedTime

   ,case when T2.tName is null

   then 'UnKnown'

   else cast(datediff(minute, T2.tTime, T1.tTime) as varchar(20))

   end as MinutesStopped

  from @t T1

   left join @t T2 on T1.tName = T2.tName

    and T2.Event = 'Stop'

    and T2.tTime < T1.tTime

  where T1.Event = 'Start'

   and (T2.tTime =

    (select max(T3.tTime)

    from @t T3

    where T3.tName = T1.tName

     and T3.Event = 'Stop'

     and T3.tTime < T1.tTime)

    or T2.tTime is null)

  order by T1.tTime

  -- PW's approach

  -- 38.02 % of cost

  select D.tName

   ,D.StartedTime

   ,case when D.PrevStop is null

   then 'UnKnown'

   else cast(datediff(minute, D.PrevStop, D.StartedTime) as varchar(20))

   end as MinutesStopped

  from (

   select T1.tName

    ,T1.tTime as StartedTime

    ,(select max(T3.tTime)

    from @t T3

    where T3.tName = T1.tName

     and T3.Event = 'Stop'

     and T3.tTime < T1.tTime) as PrevStop

   from @t T1

   where T1.Event = 'Start'

  ) D

  order by D.StartedTime

   

• Gone2mt

  Newbie

  Points: 2

  More actions

  August 18, 2006 at 12:34 pm

  #655731

  I went with PW's approach, but thank you all for your help!

• Jeff Moden

  SSC Guru

  Points: 1004340

  More actions

  August 18, 2006 at 9:12 pm

  #655762

  Here's a slightly different take...

  Anything that has a StartTime with no matching StopTime is not reported.

  Anything that is still stopped, has a stopped duration up to the current time.

  It's about the same speed as PW's good method.

  --test data

  IF OBJECT_ID('TempDB..#MyHead') IS NOT NULL

  DROP TABLE #MyHead

  Create Table #MyHead

  (

   tName varchar(20) not null

   ,Event varchar(20) not null

   ,tTime datetime not null)--,CONSTRAINT PK PRIMARY KEY (tName,Event,tTime))

  insert #MyHead

  select 'Name1', 'Start', convert(datetime, '8/16/06 8:00 AM', 0) union all

  select 'Name2', 'Start', convert(datetime, '8/16/06 8:15 AM', 0) union all

  select 'Name2', 'Stop', convert(datetime, '8/17/06 5:30 PM', 0) union all

  select 'Name3', 'Start', convert(datetime, '8/14/06 4:00 AM', 0) union all

  select 'Name1', 'Stop', convert(datetime, '8/19/06 3:00 PM', 0) union all

  select 'Name3', 'Stop', convert(datetime, '8/15/06 2:00 AM', 0) union all

  select 'Name3', 'Start', convert(datetime, '8/15/06 2:10 AM', 0) union all

  select 'Name1', 'Start', convert(datetime, '8/19/06 3:30 PM', 0)

  SELECT d.tName,

          d.Stopped_At,

          d.Started_At,

          Minutes_Stopped = DATEDIFF(mi,d.Stopped_At,ISNULL(d.Started_At,GETDATE())),

          Status   = CASE

                         WHEN d.Started_At > 0 --Faster than IS NOT NULL

                         THEN 'Successfully Restarted'

                         ELSE 'Still stopped'

                     END

     FROM (--==== Derived table "d" finds the start time for each stop time by person

           SELECT t2.tName,

                  Stopped_At   = t2.tTime,

                  Started_At = (SELECT MIN(tTime)

                                 FROM #MyHead t1

                                WHERE t1.Event = 'Start'

                                  AND t1.tName = t2.tName

                                  AND t1.tTime > t2.tTime)

             FROM #MyHead t2

            WHERE t2.Event = 'Stop'

          ) d

    ORDER BY d.tName,d.Started_At

  ------------------  Results --------------------

  tName

  Stopped_At

  Started_At

  Minutes_Stopped

  Status

  Name1

  2006-08-19 15:00:00.000

  2006-08-19 15:30:00.000

  30

  Successfully Restarted

  Name2

  2006-08-17 17:30:00.000

  NULL

  1775

  Still stopped

  Name3

  2006-08-15 02:00:00.000

  2006-08-15 02:10:00.000

  10

  Successfully Restarted

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)