Start/Stop of a Time Series

Question

Post reply

Start/Stop of a Time Series

R2ro

Default port

Points: 1439
More actions
June 23, 2006 at 11:58 am

#172804

Greetings
All,
Is there an elegant way to generate the resultset indicated from the data described below?
Input:
CREATE TABLE tmpStateSeries
(
rowSeq int,
stateDT smalldatetime,
FStateID int,
BStateID int
)
Sample Data:
rowSeq stateDT               FStateID   BStateID
1          2006.04.01 00:00 1              3
2          2006.04.01 00:15 1              3
3          2006.04.01 01:30 1              3
4          2006.04.03 08:45 1              3
5          2006.04.03 21:30 1              3
6          2006.04.04 09:30 1              1
7          2006.04.04 09:45 1              3
8          2006.04.10 13:00 2              3
9          2006.04.10 13:15 2              3
Desired resultset:
fromDate              thruDate             FStateID BStateID
2006.04.01 00:00 2006.04.03 21:30 1            3
2006.04.04 09:30 2006.04.04 09:30 1            1
2006.04.04 09:45 2006.04.04 09:45 1            3
2006.04.10 13:00 2006.04.10 13:15 2            3
The resultset essentially needs to list the start/stop dates whenever a change occurs in either the FStateID or the BStateID.
The rowSeq was included in an attempt to use rowSeq +/- 1 to find the row before the start of an F/B state change and is not required in any part of the solution.
TIA,
Art

Viewing 8 posts - 1 through 7 (of 7 total)

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

Gift Peddie SSC Guru Points: 73570 More actions · Answer 1

Try the links below for conversion codes and known issues to get started. Hope this helps.

http://www.karaszi.com/SQLServer/info_datetime.asp

http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie

R2ro Default port Points: 1439 More actions · Answer 2

Thanks but the semantics/syntax are more of the issue. I am almost certain there is a way to do this without an iterative procedural solution (i.e. pure set-based SQL nirvana)... I already have an ugly query and an interative process that both 'work' but are subject to performance issues down the road. There are currently 5M rows in the stateSeries table and it will be growing.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

CREATE TABLE #StateSeries

(

rowSeq int,

stateDT smalldatetime,

FStateID int,

BStateID int

)

Insert Into #StateSeries (rowSeq, stateDT, FStateID, BStateID)

SELECT 1, '2006-04-01 00:00', 1, 3

UNION

SELECT 2, '2006-04-01 00:15', 1, 3

UNION

SELECT 3, '2006-04-01 01:30', 1, 3

UNION

SELECT 4, '2006-04-03 08:45', 1, 3

UNION

SELECT 5, '2006-04-03 21:30', 1, 3

UNION

SELECT 6, '2006-04-04 09:30', 1, 1

UNION

SELECT 7, '2006-04-04 09:45', 1, 3

UNION

SELECT 8, '2006-04-10 13:00', 2, 3

UNION

SELECT 9, '2006-04-10 13:15', 2, 3

SELECT Min(start) as Start, ISNULL(Finish, MAX(Start)), FStateID, BStateID

FROM (

select t1.stateDT as Start, MIN(t2.stateDT) as Finish, T1.FStateID, T1.BStateID

FROM #StateSeries T1

LEFT JOIN #StateSeries t2 on (T1.FStateID T2.FStateID OR T1.BStateID T2.BStateID)

and t1.stateDT < t2.stateDT

GROUP BY t1.stateDT, T1.FStateID, T1.BStateID

) DT

GROUP BY Finish, FStateID, BStateID

ORDER BY Start

drop table #StateSeries

_____________
Code for TallyGenerator

garryurban SSC-Addicted Points: 443 More actions · Answer 4

this is just an idea:

you could use a calculated column seperate out the date and time

then use min(date) and min(Time) and group them

then UNION

and use max(date) and max(Time) and group them

and order the result of the union buy date and time

R2ro Default port Points: 1439 More actions · Answer 5

Sergiy - very, very close but the thruDate [ ISNULL(Finish, MAX(Start)) ] in your SELECT is actually the start of the next FStateID/BStateID series instead of the end of the current series.

I got to this point and introduced the rowSeq column & another self JOIN on dt.rowSeq = t3.rowSeq + 1 in an attempt to get the actual end of the FStateID/BStateID series but that left me with the last group sans thruDate (or the first group sans fromDate if I used -1).

Garry - still looking at yours...

Sergiy SSC Guru Points: 110208 More actions · Answer 6

It was so easy, I could not believe you could not modify my query to get the result you wanted:

SELECT Min(start) as Start, MAX(Start) as thruDate , FStateID, BStateID
FROM (
select t1.stateDT as Start, MIN(t2.stateDT) as Finish, T1.FStateID, T1.BStateID
FROM #StateSeries T1
LEFT JOIN #StateSeries t2 on (T1.FStateID <> T2.FStateID OR T1.BStateID <> T2.BStateID)
and t1.stateDT < t2.stateDT
GROUP BY t1.stateDT, T1.FStateID, T1.BStateID
) DT
GROUP BY Finish, FStateID, BStateID
ORDER BY Start

That's it.

Don't be afraid to play with smbdy's else suggessions. It's not a chapter from Bible, you can change it in any way you like.

_____________
Code for TallyGenerator

R2ro Default port Points: 1439 More actions · Answer 7

Sergiy,

If you think you can't believe it you won't believe how unbelievable it is to me! I played with that thing until my eyes bled!! I guess I couldn't see the forest for the trees...

Of course, there are more trees in this forest than what I have divulged and this is part of the decomposition of a query that currently contains DATEADDs in the JOINs, DATEPARTs in the WHERE, CASEs in the SELECT referencing views of views of views (yes, 3 layers deep!).

Thank you