SQL Looping

[SQLHelp01]

Hello Everyone,

I am using SQL 2008, R2

/* This is result in my #Transactions Table
YSLN YTSEQ Category
100 65 P&I
100 66 P&I Adj
100 67 P&I Adj
100 68 P&I
100 69 P&I Adj

CREATE TABLE #Adjustments (
ID int IDENTITY
, LoanNumber varchar(10)
, TranSeqAdj int
, TranSeqNonAdj int
, Category varchar(10)
, CategoryNonAdj varchar(10)
)
INSERT INTO #Adjustments (LoanNumber, TranSeqAdj, Category)
SELECT
YSLN
, YTSEQ
, Category
FROM #Transactions
WHERE Category = 'P&I Adj'

/* This is the Result:
ID LoanNumber TranSeqAdj TranSeqNonAdj Category CategoryNonAdj
8 100 66 NULL P&I Adj NULL
9 100 67 NULL P&I Adj NULL
10 100 69 NULL P&I Adj NULL



/* This is 1st Loop
DECLARE
@MAX_CTR int
, @CTR int = 1

SET @MAX_CTR = (SELECT COUNT(LoanNumber) FROM #Adjustments)


WHILE @CTR <= @MAX_CTR
BEGIN
UPDATE ADJ
SET TranSeqNonAdj = NONADJ.TranSeqNonAdj
, CategoryNonAdj = 'P&I Adj'
FROM #Adjustments ADJ
JOIN (
SELECT
LoanNumber = TRANS.YSLN
, LoanID = ADJUST.ID
, TranSeqAdj = MIN(ADJUST.TranSeqAdj)
, TranSeqNonAdj = MAX(TRANS.YTSEQ)
FROM #Transactions TRANS
JOIN #Adjustments ADJUST ON TRANS.YSLN = ADJUST.LoanNumber
AND ADJUST.ID = @CTR
LEFT JOIN #Adjustments ADJCHK ON TRANS.YSLN = ADJCHK.LoanNumber
AND TRANS.YTSEQ = ADJCHK.TranSeqNonAdj
WHERE TRANS.Category = 'P&I'
AND ADJCHK.LoanNumber IS NULL
GROUP BY
TRANS.YSLN
, ADJUST.ID
HAVING MAX(TRANS.YTSEQ) < MIN(ADJUST.TranSeqAdj)
) NONADJ ON ADJ.LoanNumber = NONADJ.LoanNumber
AND ADJ.TranSeqAdj = NONADJ.TranSeqAdj
AND ADJ.ID = NONADJ.LoanID

UPDATE ADJ
SET TranAmtNonAdj = TRN.YPRAM
, Category = 'P&I Adj'
FROM #Adjustments ADJ
JOIN #Transactions TRN ON ADJ.LoanNumber = TRN.YSLN
AND ADJ.TranSeqNonAdj = TRN.YTSEQ
WHERE ADJ.ID = @CTR

SET @CTR = @CTR + 1

END


ID LoanNumber TranSeqAdj TranSeqNonAdj Category CategoryNonAdj
8 100 66 NULL P&I Adj NULL
9 100 67 NULL P&I Adj NULL
10 100 69 68 P&I Adj P&I Adj



/* This is the 2nd Loop
INSERT #AdjustmentsODDS (LoanID, LoanNumber, TranSeqAdj, TranSeqNonAdj, Category)
SELECT
ID
, LoanNumber
, TranSeqAdj
, TranSeqNonAdj
, Category
FROM #Adjustments
WHERE TranSeqNonAdj IS NULL
ORDER BY
LoanNumber
, TranSeqAdj DESC

SET @MAX_CTR = (SELECT COUNT(LoanNumber) FROM #AdjustmentsODDS)
SET @CTR = 1

WHILE @CTR <= @MAX_CTR
BEGIN
UPDATE ADJ
SET TranSeqNonAdj = NONADJ.TranSeqNonAdj
, CategoryNonAdj = 'P&I Adj'
FROM #AdjustmentsODDS ADJ
JOIN (
SELECT
LoanNumber = TRANS.YSLN
, LoanID = ADJUST.ID
, TranSeqAdj = MIN(ADJUST.TranSeqAdj)
, TranSeqNonAdj = MIN(TRANS.YTSEQ)
FROM #Transactions TRANS
JOIN #AdjustmentsODDS ADJUST ON TRANS.YSLN = ADJUST.LoanNumber
AND ADJUST.ID = @CTR AND ADJUST.TranSeqNonAdj IS NULL
LEFT JOIN #Adjustments ADJCHK ON TRANS.YSLN = ADJCHK.LoanNumber
AND TRANS.YTSEQ = ADJCHK.TranSeqNonAdj
LEFT JOIN #AdjustmentsODDS ADJCHK2 ON TRANS.YSLN = ADJCHK2.LoanNumber
AND TRANS.YTSEQ = ADJCHK2.TranSeqNonAdj
WHERE TRANS.Category = 'P&I'
AND ADJCHK.LoanNumber IS NULL
AND ADJCHK2.LoanNumber IS NULL
GROUP BY
TRANS.YSLN
, ADJUST.ID
HAVING MIN(TRANS.YTSEQ) > MIN(ADJUST.TranSeqAdj)
) NONADJ ON ADJ.LoanNumber = NONADJ.LoanNumber
AND ADJ.TranSeqAdj = NONADJ.TranSeqAdj
AND ADJ.ID = NONADJ.LoanID

UPDATE ADJ
SET TranAmtNonAdj = TRN.YPRAM
, Category = 'P&I Adj'
FROM #AdjustmentsODDS ADJ
JOIN #Transactions TRN ON ADJ.LoanNumber = TRN.YSLN
AND ADJ.TranSeqNonAdj = TRN.YTSEQ
WHERE ADJ.ID = @CTR

UPDATE ADJ
SET
TranSeqNonAdj = ADJODD.TranSeqNonAdj
, CategoryNonAdj = ADJODD.CategoryNonAdj
FROM #Adjustments ADJ
JOIN #AdjustmentsODDS ADJODD ON ADJ.LoanNumber = ADJODD.LoanNumber
AND ADJ.ID = ADJODD.LoanID
WHERE ADJODD.ID = @CTR

SET @CTR = @CTR + 1

END


SELECT
LoanNbr = T.YSLN
, TranSeqNbr = T.YTSEQ
, TranType = T.YTYPE
, Category = CASE
WHEN ADJ.CategoryNonAdj IS NULL
THEN T.Category
ELSE ADJ.CategoryNonAdj
END


FROM #Transactions T
LEFT JOIN #Adjustments ADJ ON T.YSLN = ADJ.LoanNumber
AND T.YTSEQ = ADJ.TranSeqNonAdj

This is the Final result:
LoanNumber TranSeqNbr TranType Category
100 65 2 P&I
100 66 12 P&I Adj
100 67 93 P&I Adj
100 68 2 P&I Adj
100 69 12 P&I Adj


I am trying to do a look back match, which should match tran # 66 and #65 so it would update tran #65 with Category of P&I to P&I Adj. But has no luck in doing so. Any help is greatly appreciated.

 

[kjv1611]

I can't say I'd be able to help you get this sorted out, as I've got a LOT to learn yet with SQL. However, I will say I'd suggest you provide more information. Basically, you're saying "it's not working" - how about telling us how. Example:

1. What Error messages if any?
2. Results different than what you expect?
3. What results do you expect?
4. etc..

 

[SQLHelp01]

I am sorry. Basically, I also need a look back match where I need it to match tran # 65 also so it would update my record from P&I to P&I Adj. Thanks so much for looking.