Querying for time periods

Question

Post reply

Querying for time periods

mstephens

Mr or Mrs. 500

Points: 525
More actions
March 23, 2006 at 10:11 am

#111496

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

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 · Answer 2

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