Querying for time periods

• Jeff Moden

  SSC Guru

  Points: 1004664

  More actions

  March 23, 2006 at 6:00 pm

  #628494

  Here's an example...

  CREATE TABLE #yourtable (Time DATETIME,VALUE INT)

  INSERT INTO #yourtable(Time,Value) VALUES('10:15:00',1)

  INSERT INTO #yourtable(Time,Value) VALUES('10:15:10',0)

  INSERT INTO #yourtable(Time,Value) VALUES('10:15:20',1)

  INSERT INTO #yourtable(Time,Value) VALUES('10:15:30',0)

  INSERT INTO #yourtable(Time,Value) VALUES('10:15:40',1)

  INSERT INTO #yourtable(Time,Value) VALUES('10:15:50',0)

   SELECT StartTime = CONVERT(CHAR(8),t1.Time,108),

          EndTime   = (SELECT CONVERT(CHAR(8),MIN(t2.Time),108)

                         FROM #yourtable t2

                        WHERE t2.Value = 0

                          AND t2.Time >= t1.Time)

     FROM #yourtable t1

    WHERE t1.Value = 1

    ORDER BY t1.Time

  However, it's only good for one day and unless you can guarantee that each start time (1) will be followed by and end time (0), the returns could get seriously out of whack.

  Might I suggest that your "Time" column be made to hold the date AND time to allow this to run over a multi-day period.  You'll still have the problem of every start time having to be followed by an end time.

  --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)
  

• [KH]

  SSCommitted

  Points: 1908

  More actions

  March 23, 2006 at 9:16 pm

  #628503

  CREATE TABLE #yourtable (Time DATETIME,Value INT)

  create index i_yourtable on #yourtable(Time)

  INSERT INTO #yourtable(Time,Value) ValueS('10:15:00',1)

  INSERT INTO #yourtable(Time,Value) ValueS('10:15:10',0)

  INSERT INTO #yourtable(Time,Value) ValueS('10:15:20',1)

  INSERT INTO #yourtable(Time,Value) ValueS('10:15:30',0)

  INSERT INTO #yourtable(Time,Value) ValueS('10:15:40',1)

  INSERT INTO #yourtable(Time,Value) ValueS('10:15:50',0)

  selectstart_time = convert(varchar(8), s.Time, 108), end_time = convert(varchar(8), min(e.Time), 108)

  from#yourtable s inner join #yourtable e

  ons.Time< e.Time

  wheres.Value= 1

  ande.Value= 0

  group by s.Time

  drop table #yourtable