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.
| Author |
Topic |
|
ChetShah
Starting Member
37 Posts |
 Posted - 2002-01-09 : 06:47:55
|
| The following query shows movements for each liabilityselect t1.LiabilityID,t1.DateTimePassed,t1.PassedTofrom tblliabilitymovement as t1Order BY t1.LiabilityID,t1.DateTimePassed DESCand results are:LiabilityID DateTimePassed PassedTo ----------- --------------------------- ----------- 11431 Nov 21 2001 5:43PM 5 11431 Nov 21 2001 5:41PM 4 11431 Nov 4 2001 1:11PM 7 I'm trying to generate a query whereby - each liabilityID is compared with next row to check if it's the same - if they are the same then generate these results from PassedTo field is transposed :LiabilityID FromTeam ToTeam ----------- ------------- ----------- 11431 4 511431 7 411431 0 7Is this possible to achieve without using cursorsChet |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2002-01-09 : 09:16:47
|
| Hi, this will do it.First I create a ranking for each liability by the Date it was passed. I would create this as a view for simplicity's sake. then Join that on Rank = Rank+1. SELECT X.LiabilityID, X.DatetimePassed, ISNULL(Y.PassedTo,0) AS "PassedFrom",X.PassedToFROM ( SELECT A.LiabilityID, A.DatetimePassed, A.PassedTo, Count(*) as "Rank" FROM #tblliabilitymovement A JOIN #tblliabilitymovement B ON A.LiabilityID = B.LiabilityID AND A.DatetimePassed > = b.DatetimePassed GROUP BY A.LiabilityID, A.DatetimePassed, A.PassedTo ) AS X Left OUTer JOIN (SELECT A.LiabilityID, A.DatetimePassed, A.PassedTo, Count(*) as "Rank" FROM #tblliabilitymovement A JOIN #tblliabilitymovement B ON A.LiabilityID = B.LiabilityID AND A.DatetimePassed > = b.DatetimePassed GROUP BY A.LiabilityID, A.DatetimePassed, A.PassedTo ) AS Y ON X.LiabilityID = Y.LiabilityID AND X.Rank = Y.Rank+1 |
 |
|
|
|
|
|
|
|
Row Comparison