CODEWITHSUNDEEP
sql-server-2008
Richard Gale
Richard Gale

Reputation: 1952

SQL Server Loop Backwards through results set

I have been given a task to generate an arrears report for accounts.

This is fairly straight forward, apart from 1 column, DaysInArrears.

I have created a query which works out the arrears status of an account at each scheduled payment.

This query populates a temporary table (#ArrearsAnalysis) with the results similar to below:

AgreementID DueDate     AmountDue DueTD  PaidTD FirstPaymentDue ArrearsStatus   RN
184         2013-03-11  75.00     75.00  0.00   2013-03-11      1               1
184         2013-03-25  75.00     150.00 0.00   2013-03-11      1               2
184         2013-04-08  75.00     225.00 300.00 2013-03-11      0               3
184         2013-04-22  75.00     300.00 300.00 2013-03-11      0               4
184         2013-05-03  75.00     375.00 300.00 2013-03-11      1               5
184         2013-05-20  75.00     450.00 300.00 2013-03-11      1               6

I also have another Temporary Table (#ArrearsCheck) populated from the following query:

INSERT INTO #ArrearsCheck
SELECT 
    AgreementID, 
    SUM(ArrearsStatus),
    MAX(RN)
FROM 
    #ArrearsAnalysis 
GROUP BY 
    AgreementID

What I then need to do is look backwards through the rows to work out when they fell into the latest Arrears (Arrears Score 1)

So for the example above I would expect a result of 03/05/2013.

If an account has never had a payment, then I would return the FirstPaymentDue Date. If an account has never been in arrears then I would just return NULL

Here is my query so far dealing with Non Payers and Non Arrears, I just need help with the looping to work out the above (highlighted with * *):

SELECT
    DISTINCT(A.AgreementID),
    CASE WHEN (C.ArrearsTally <= 0) 
    THEN NULL 
    ELSE (CASE WHEN (C.ArrearsTally = C.PaymentCount) 
          THEN NULL 
          *ELSE 0* 
          END) 
    END AS ArrearsDate
FROM
    #ArrearsAnalysis AS A
LEFT OUTER JOIN
    #ArrearsCheck AS C ON A.AgreementID = C.AgreementID

The result of this query I will then pass to my next query which will calculate the difference (days) between today and the result date.

Upvotes: 0

Views: 966

Answers (1)

criticalfix
criticalfix

Reputation: 2870

If we can trust that RN (Row Number?) is sequentially numbered, then we are looking for two adjacent records where the more recent record has ArrearsStatus = 1 and the preceding one has ArrearsStatus = 0 (or is absent, e.g. NULL). We want the date of the ArrearsStatus = 1 record associated with the most recent such event:

SELECT MAX(aa1.DueDate) AS MaxDueDate
FROM #ArrearsAnalysis aa1
LEFT JOIN #ArrearsAnalysis aa2
ON aa2.RN = aa1.RN - 1
WHERE aa1.ArrearsStatus = 1
AND (aa2.ArrearsStatus = 0 OR aa2.ArrearsStatus IS NULL)

If we can't trust RN, we would have to start with a join condition based on the date (ON aa2.DueDate < aa1.DueDate) and then find some other way to establish adjacency. (Possibly a third self-join on an intermediate date, and then insist that the intermediate date be NULL...)

A set-based approach is usually a better approach than looping. In this situation I would use a cursor loop only if the data ends up making a set-based approach impractical.

You can add an aa1.AgreementID grouping. I don't think we need #ArrearsCheck.

UPDATE

The answer above applies to the question originally phrased as "work out when they fell into the latest Arrears". A question about the "earliest arrears" is actually a little bit more interesting. We cannot just switch to a MIN(), because we wish to see the initial DueDate only if the AgreementID has been in arrears from the start, and there has never been a transition from 0 to 1.

The most straightforward solution looks to me to find the MIN() date for each case, and then pick the MAX() of those two values. Nulls should drop out of the aggregate.

SELECT b.AgreementID,
MAX(b.MinDueDate) AS FirstArrearsDate
FROM (
SELECT aa1.AgreementID,
MIN(aa1.DueDate) AS MinDueDate
FROM #ArrearsAnalysis aa1
LEFT JOIN #ArrearsAnalysis aa2
ON aa2.RN = aa1.RN - 1
WHERE aa1.ArrearsStatus = 1
AND aa2.ArrearsStatus = 0
GROUP BY aa1.AgreementID
UNION ALL
SELECT aa1.AgreementID,
MIN(aa1.DueDate) AS MinDueDate
FROM #ArrearsAnalysis aa1
LEFT JOIN #ArrearsAnalysis aa2
ON aa2.RN = aa1.RN - 1
WHERE aa1.ArrearsStatus = 1
AND aa2.ArrearsStatus IS NULL
GROUP BY aa1.AgreementID
) AS b
GROUP BY b.AgreementID

Upvotes: 1

Related Questions