How to get the previous ID from this query

Question

Post reply

How to get the previous ID from this query

Nomvula

Hall of Fame

Points: 3778
More actions
April 28, 2010 at 7:41 am

#234453

hi
i have the following query
UPDATE AVR_Curr
SET PrevTransactionID = (SELECT max(AVR.TransactionID)
FROM @tblAssetValues AVR
WHERE AVR.RowID < AVR_Curr.RowID
AND AVR.DepreciationLinksID = AVR_Curr.DepreciationLinksID
AND AVR.AssetID = AVR_Curr.AssetID )
FROM @tblAssetValues AVR_Curr
and i need my results to appear as this table:
AssetCodeDepreciationLinksIDPeriodIDTransactionIDPrevTransactionID
04805326251035227504NULL
04805326251035260504260503
04805326251049227578227504
04805326251049231878227578
04805326251049260503259837
04805326251050236967231878
04805326251051241514236967
04805326251052246072241514
04805326251053250645246072
04805326251054255237250645
04805326251055259837255237
but the PrevTransactionID column is wrong, it should be
NULL
227504
260504
227578
231878
260503
236967
241514
246072
250645
255237

Viewing 3 posts - 1 through 2 (of 2 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

The code you're running is basically a triangular join which probably doesn't scale very well...

You should look into ROW_NUMBER().

Something like this (untested, since ne ready to use sample data to test against...)

WITH cte AS

(

SELECT

ROW_NUMBER() OVER(PARTITION BY AssetCode, DepreciationLinksID ORDER BY PeriodID, TransactionID ) AS row_nr,

AssetCode,

DepreciationLinksID,

TransactionID

FROM @tblAssetValues

),

sub AS

(

SELECT

cte1.AssetCode,

cte1.DepreciationLinksID,

cte1.TransactionID,

cte2.TransactionID AS PrevId

FROM cte cte1

INNER JOIN cte cte2

ON cte1.AssetCode=cte2.AssetCode

AND cte1.DepreciationLinksID=cte2.DepreciationLinksID

AND cte1.row=cte2.row-1

)

UPDATE AVR_Curr

SET PrevTransactionID = sub.PrevId

FROM @tblAssetValues AVR_Curr

INNER JOIN sub

ON AVR_Curr.AssetCode=sub.AssetCode

AND AVR_Curr.DepreciationLinksID=sub.DepreciationLinksID

AND AVR_Curr.TransactionID=sub.TransactionID

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Nomvula Hall of Fame Points: 3778 More actions · Answer 2

you are such a star....

it worked perfectly, i only had to change the minus to plus sign here: AND cte1.row_nr=cte2.row_nr+1.

thanks a million