Query to find traversed points of events

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query to find traversed points of events

Author

Topic

sweha
Starting Member

5 Posts

Posted - 2012-05-25 : 16:54:00

I have sequence of events in table or ascending order
Sequence Event Point
1 A 1
2 A 2
3 A 3
4 B 4
5 B 5
6 C 6
7 C 7
8 C 6
9 B 5
10 B 4
11 A 3
12 A 2
13 A 1

I need result to look like this
Event BeginPoint EndPoint
A 1 3
B 4 5
C 6 7
C 7 6
B 5 4
A 3 1

I am fairly new to sql.
Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 17:01:34

[code]
;With Deltas
AS
(
SELECT Sequence, Event, Point,MinSeq,
ROW_NUMBER() OVER (PARTITION BY Event,MinSeq ORDER BY Sequence) AS Rn,
ROW_NUMBER() OVER (PARTITION BY Event,MinSeq ORDER BY Sequence DESC) AS RnRev
FROM Table t
CROSS APPLY (SELECT MIN(Sequence) AS MinSeq
FROM Table
WHERE Event <> t.Event
AND Sequence > t.Sequence
)t1
)

SELECT Event,
MAX(CASE WHEN Rn = 1 THEN Point END) AS BeginPoint,
MAX(CASE WHEN RnRev = 1 THEN Point END) AS EndPoint
FROM Deltas
WHERE Rn = 1
OR RnRev = 1
GROUP BY Event
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sweha
Starting Member

5 Posts

Posted - 2012-05-25 : 17:39:19

Thanks, I tried but it does not bring the result in the way I want.
Here is my table
EventSequence EventsName PointsTraversed
1 A 1
2 A 2
3 A 3
4 B 4
5 B 5
6 C 6
7 C 7
8 C 6
9 B 5
10 B 4
11 A 3
12 A 2
13 A 1

In the result, each event should be accounted for with a begin and end point.
for eg.
A 1 3
B 4 5
C 6 7
C 7 6
B 5 4
A 3 1

Here is your query, i substituted the colum names
;With Deltas
AS
(
SELECT EventSequence, EventsName, PointsTraversed,MinSeq,
ROW_NUMBER() OVER (PARTITION BY EventsName,MinSeq ORDER BY EventSequence) AS Rn,
ROW_NUMBER() OVER (PARTITION BY EventsName,MinSeq ORDER BY EventSequence DESC) AS RnRev
FROM #tempEvents t
CROSS APPLY (SELECT MIN(EventSequence) AS MinSeq
FROM #tempEvents
WHERE EventsName <> t.EventsName
AND EventSequence > t.EventSequence
)t1
)

SELECT EventsName,
MAX(CASE WHEN Rn = 1 THEN PointsTraversed END) AS BeginPoint,
MAX(CASE WHEN RnRev = 1 THEN PointsTraversed END) AS EndPoint
FROM Deltas
WHERE Rn = 1
OR RnRev = 1
GROUP BY EventsName

Here is the result it produces
EventsName BeginPoint EndPoint
A 3 3
B 5 5
C 6 6

Thanks!

quote:

Originally posted by visakh16


;With Deltas
AS
(
SELECT Sequence, Event, Point,MinSeq,
ROW_NUMBER() OVER (PARTITION BY Event,MinSeq ORDER BY Sequence) AS Rn,
ROW_NUMBER() OVER (PARTITION BY Event,MinSeq ORDER BY Sequence DESC) AS RnRev
FROM Table t
CROSS APPLY (SELECT MIN(Sequence) AS MinSeq
             FROM Table
             WHERE Event <> t.Event 
             AND Sequence > t.Sequence
             )t1
)

SELECT Event,
MAX(CASE WHEN Rn = 1 THEN Point END) AS BeginPoint,
MAX(CASE WHEN RnRev = 1 THEN Point END) AS EndPoint
FROM Deltas
WHERE Rn = 1
OR RnRev = 1
GROUP BY Event

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 19:40:04

can you explain how both below rows came?

C 6 7
C 7 6

shouldnt it be just

C 6 6

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-25 : 19:59:15

sorry there was a slight typo


;With Deltas
AS
(
SELECT Sequence, Event, Point,MinSeq,
ROW_NUMBER() OVER (PARTITION BY Event,MinSeq ORDER BY Sequence) AS Rn,
ROW_NUMBER() OVER (PARTITION BY Event,MinSeq ORDER BY Sequence DESC) AS RnRev
FROM Table t
CROSS APPLY (SELECT MIN(Sequence) AS MinSeq
             FROM Table
             WHERE Event <> t.Event 
             AND Sequence > t.Sequence
             )t1
)

SELECT Event,
MAX(CASE WHEN Rn = 1 THEN Point END) AS BeginPoint,
MAX(CASE WHEN RnRev = 1 THEN Point END) AS EndPoint
FROM Deltas
WHERE Rn = 1
OR RnRev = 1
GROUP BY Event,MinSeq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sweha
Starting Member

5 Posts

Posted - 2012-05-26 : 01:53:55

Thank you for your reply. I will try and let you know.
The reason C appears twice is I have to consider both forward and reverse paths. Similar to A and B, C also moves from 6 to 7 and then gets back to 6, so two times.
The only difference between C and the other events is that, other events don't start reversing unless a new event occurs, while C starts reversing immediately without occurrence of any new events after itself.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-26 : 22:04:27

quote:
Originally posted by sweha

Thank you for your reply. I will try and let you know.
The reason C appears twice is I have to consider both forward and reverse paths. Similar to A and B, C also moves from 6 to 7 and then gets back to 6, so two times.
The only difference between C and the other events is that, other events don't start reversing unless a new event occurs, while C starts reversing immediately without occurrence of any new events after itself.

so you mean you need to consider direction as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sweha
Starting Member

5 Posts

Posted - 2012-06-09 : 14:59:40

Yes. Increasing number means positive direction and decreasing number means negative direction.

quote:
Originally posted by visakh16

quote:
Originally posted by sweha

Thank you for your reply. I will try and let you know.
The reason C appears twice is I have to consider both forward and reverse paths. Similar to A and B, C also moves from 6 to 7 and then gets back to 6, so two times.
The only difference between C and the other events is that, other events don't start reversing unless a new event occurs, while C starts reversing immediately without occurrence of any new events after itself.

so you mean you need to consider direction as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-06-11 : 16:41:55

Hi.
Sorry for the late reply.
I was really puzzled by this, as I don't think this can be done in one sql. I would generate the output in the program, and not let the sql server generate it, as this (I think) is a very unusual output.

Never the less, I came close to the output with the sql below. I couldn't make it output lines, when changing direction when the next event is the same. But instead you can view minimum and maximum point value within the "block". Hope this can be used, although this isn't exactly what you wanted.

Output of the sql is:


Event BeginPoint EndPoint MinPoint MaxPoint
A     1          3        1        3
B     4          5        4        5
C     6          6        6        7
B     5          4        4        5
A     3          1        1        3


select t1.event
      ,t1.point as beginpoint
      ,t2.point as endpoint
      ,min(t3.point) as minpoint
      ,max(t3.point) as maxpoint
  from (select t1.sequence as beginsequence
              ,t1.event
              ,t1.point
              ,t3.sequence as endsequence
          from table as t1
               left outer join table as t2
                            on t2.sequence=t1.sequence-1
                           and t2.event=t1.event
               left outer join (select t1.sequence
                                      ,t1.event
                                      ,t1.point as endpoint
                                  from table as t1
                                  left outer join table as t2
                                               on t2.sequence=t1.sequence+1
                                              and t2.event=t1.event
                                 where t2.sequence is null
                               ) as t3
                            on t3.sequence>t1.sequence
                           and t3.event=t1.event
         where t2.sequence is null
          group by t1.sequence
                  ,t1.event
                  ,t1.point
       ) as t1
       inner join table as t2
               on t2.sequence=t1.endsequence
       inner join table as t3
               on t3.sequence between t1.beginsequence and t1.endsequence
 group by t1.event
         ,t1.point
         ,t2.point
 order by t1.beginsequence

I know the sql isn't pretty and is also a database killer on large tables.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources