Querying for time periods

• mstephens

  Mr or Mrs. 500

  Points: 525

  More actions

  March 23, 2006 at 3:22 am

  #365580

  I have a problem where i have some digital data with a time value indicating when the digital value changed to either the true or false state. What i need to do is query this and get the start time and end time for the periods where the digital value is true.

  So for example if i had the table:

  Time         Value

  10:15:00     1

  10:15:10     0

  10:15:20     1

  10:15:30     0

  10:15:40     1

  10:15:50     0

  How can i query this to end up with the following results for time periods when Value is 1?

  StartTime   EndTime

  10:15:00    10:15:10

  10:15:20    10:15:30

  10:15:40    10:15:50

  Many thanks

  Matt

• Jo Pattyn

  SSC-Dedicated

  Points: 32348

  More actions

  March 23, 2006 at 11:38 am

  #628436

  Timetable

  Timevalue datetime (time)

  IsStart bit (value)

  select Timetable.timevalue as starttime,min(table2.timevalue) as endtime

  from Timetable

  inner join Timetable table2

  on table2.timevalue>Timetable.timevalue /*younger than*/

  and table2.isstart=0

  and Timetable.isstart=1

  group by Timetable.timevalue

  Renamed Time & Value because they are reserved words.

  With some functions you can strip of the datepart.