| Author |
 Topic  |
|
|
sweha
Starting Member
5 Posts |
 Posted - 05/25/2012 : 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
India
47173 Posts |
Posted - 05/25/2012 : 17:01:34
|
;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/
|
Edited by - visakh16 on 05/25/2012 17:05:55 |
 |
|
|
sweha
Starting Member
5 Posts |
Posted - 05/25/2012 : 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
India
47173 Posts |
Posted - 05/25/2012 : 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
India
47173 Posts |
Posted - 05/25/2012 : 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 - 05/26/2012 : 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
India
47173 Posts |
Posted - 05/26/2012 : 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 - 06/09/2012 : 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
Posting Yak Master
Denmark
100 Posts |
Posted - 06/11/2012 : 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. |
|
|
| |
Topic |
|
|
|
Query to find traversed points of events
