CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Fandango68
Fandango68

Reputation: 4898

How to PARTITION every 2nd record based on similar data?

I have a table like this...

enter image description here

What I am after is a way in SQL to achieve this...

enter image description here

The red line I don't need as I've effectively shifted the dates in 'end_date' column up by 1. ie: I want the row discarded or ignored

The dates are based on Start and End date - of the 2nd row or the next row with the FROMCODE and TOCODE opposite each other. So to explain better, what I am trying to do is GROUP BY every two rows there the FROMCODE = the TOCODE of the next row and for consistency, I want to make sure the TOCODE of the first row = the FROMCODE of the 2nd row, and so on.

I hope I am making myself clear.

I've tried using a recursive JOIN, to link the next row using the FROMCODE = TOCODE of the sub JOIN and TOCODE = FROMCODE of the subjoin, plus making sure the record has the same VOYAGE_ID and the RANK is greater than the 1st row's RANK. But all I get is gibberish.

Upvotes: 0

Views: 68

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

You can solve this using a left join -- assuming the ranks have no gaps:

select t.*,
       tnext.end_date as new_end_date
from t join
     t tnext
     on tnext.voyage_id = t.voyage_id and tnext.rank = t.rank + 1;

Note that the join removes the last row as well as finds the next end date.

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use outer apply :

select t.*, tt.end_date as new_end_date
from table t outer apply (
      select top 1 t1.end_date
      from table t1
      where t1.voyage_id = t.voyage_id and
            t1.rank > t.rank
      order by t1.rank
) tt
where tt.end_date is not null;

Upvotes: 1

Related Questions