CODEWITHSUNDEEP
sqlsql-servert-sql
Andrey
Andrey

Reputation: 95

Find out the date of the current debt in SQL

Please, help me figure out how to find out the date of the currently debt and the number of days since its inception I have this table:

Date Customer Deal Sum
20.11.2009 220000 222221 25000
27.11.2009 220001 222221 -30000
20.12.2009 220000 222221 20000
31.12.2009 220001 222221 -10000
12.12.2009 111110 111111 12000
25.12.2009 111110 111111 5000
12.01.2010 111110 111111 -10100
12.12.2009 111110 122222 10000
29.12.2009 111110 122222 -10000

On the loan, payments can be made by co-borrowers. If a client with a loan misses the next payment on schedule, he has a debt. In this case, a corresponding record appears in the table, where Sum is the unpaid amount (with a positive sign). If, then, the client makes a payment (the full amount or part of it), a new record appears, where Sum is the amount paid (with a “-” sign). It should be noted that the client's payment does not necessarily completely extinguish the accumulated debt, it can only be part of the debt.

DROP TABLE IF EXISTS #PDCL
set dateformat dmy
CREATE TABLE #PDCL
(
Payment_dt date,
Customer int,
Deal int,
Currency varchar(5),
Sum_payment int
)
INSERT INTO #PDCL VALUES ('12.12.2009', 111110, 111111, 'RUR', 12000)
INSERT INTO #PDCL VALUES ('25.12.2009', 111110, 111111, 'RUR', 5000)
INSERT INTO #PDCL VALUES ('12.12.2009', 111110, 122222, 'RUR', 10000)
INSERT INTO #PDCL VALUES ('12.01.2010', 111110, 111111, 'RUR', -10100)
INSERT INTO #PDCL VALUES ('20.11.2009', 220000, 222221, 'RUR', 25000)
INSERT INTO #PDCL VALUES ('20.12.2009', 220000, 222221, 'RUR', 20000)
INSERT INTO #PDCL VALUES ('31.12.2009', 220001, 222221, 'RUR', -10000)
INSERT INTO #PDCL VALUES ('29.12.2009', 111110, 122222, 'RUR', -10000)
INSERT INTO #PDCL VALUES ('27.11.2009', 220001, 222221, 'RUR', -30000)

--Start date of the current debt
SELECT Deal
     , MIN(Payment_dt) AS Start_date_current_debt
FROM #PDCL
WHERE Sum_payment > 0
GROUP BY Deal

--Number of days of current debt
SELECT Deal
     , DATEDIFF(d, MIN(Payment_dt), MAX(Payment_dt)) AS Num_days_current_debt
FROM #PDCL
GROUP BY Deal

The dataset has many different Customers and Deal. I gave an illustrative example, because of which the question arose. In it, the client was twice in debt.

My desired answer:

Deal Start_date_current_debt
111111 2009-12-12
122222 2009-12-12
222221 2009-12-20
Deal Num_days_current_debt
111111 todate - 2009-12-12
122222 17
222221 todate - 2009-12-20

Upvotes: 0

Views: 308

Answers (1)

Bjorg P
Bjorg P

Reputation: 1073

After reading the comments on this answer, here is an approach that solves the question asked. I have taken a slightly verbose approach so that you can follow the logic, but feel free to collapse some of the common table expressions to make it shorter.

We can compute the running SUM for each deal, and I will number the rows for each deal. We can then compare the SUM for a current row of a deal to the SUM of a previous row of a deal using LAG. When the SUM goes positive from negative, or the sum is positive and the previous SUM is NULL, we have found where there is a debt crossing. I will multiply the row number by -1 in these situations so that I can find the MIN row number for each deal and that will be the most recent date when there was money owed. As I mentioned, this can be shortened but I left it a bit verbose so you can follow the logic:

;WITH sums AS (
    SELECT Deal, 
        Payment_Dt, 
        SUM(Sum_payment) OVER (PARTITION BY Deal ORDER BY Payment_dt) AS [currentSum], 
        ROW_NUMBER() OVER (PARTITION BY Deal ORDER BY Payment_dt)  AS [num]
    FROM #PDCL
), sumsWithLag AS (
    SELECT Deal, Payment_dt, 
    currentSum,  
    LAG(currentSum) OVER (PARTITION BY Deal ORDER BY Payment_dt)  AS [prevSum], 
    num
    FROM sums
), markedCrossings AS (
    SELECT Deal, Payment_dt, 
    CASE WHEN currentSum > 0 AND (prevSum IS NULL OR prevSum < 0) THEN -1 ELSE 1 END * num AS num
    FROM sumsWithLag
), debtCrossings AS (
    SELECT Deal, MIN(num) AS num 
    FROM markedCrossings 
    GROUP BY Deal
)
SELECT s.Deal, s.Payment_dt AS Start_date_current_debt
FROM debtCrossings AS c 
INNER JOIN sums AS s ON s.Deal = c.Deal and s.num = ABS(c.num)

And it gives this result:

Deal Start_date_current_debt
111111 2009-12-12
122222 2009-12-12
222221 2009-12-20

Those are the expected values. At this point, we can use the same common table expressions to answer the number of days in debt. We know the start date, so we just have to see if the deal has a positive amount at the most recent sum.

;WITH sums AS (
    SELECT Deal, 
        Payment_Dt, 
        SUM(Sum_payment) OVER (PARTITION BY Deal ORDER BY Payment_dt) AS [currentSum], 
        ROW_NUMBER() OVER (PARTITION BY Deal ORDER BY Payment_dt)  AS [num]
    FROM #PDCL
), sumsWithLag AS (
    SELECT Deal, Payment_dt, 
    currentSum,  
    LAG(currentSum) OVER (PARTITION BY Deal ORDER BY Payment_dt)  AS [prevSum], 
    num
    FROM sums
), markedCrossings AS (
    SELECT Deal, Payment_dt, 
    CASE WHEN currentSum > 0 AND (prevSum IS NULL OR prevSum < 0) THEN -1 ELSE 1 END * num AS num
    FROM sumsWithLag
), debtCrossings AS (
    SELECT Deal, MIN(num) AS num 
    FROM markedCrossings 
    GROUP BY Deal
), startDates AS (
    SELECT s.Deal, s.Payment_dt AS Start_date_current_debt
    FROM debtCrossings AS c 
    INNER JOIN sums AS s ON s.Deal = c.Deal and s.num = ABS(c.num)
), balances AS (
    SELECT Deal, SUM(Sum_payment) AS balance, MAX(Payment_dt) AS Payment_dt
    FROM #PDCL 
    GROUP BY Deal
) 
SELECT s.Deal, 
DATEDIFF(day, s.Start_date_current_debt, CASE WHEN b.balance > 0 THEN GETDATE() ELSE b.Payment_dt END) AS Num_days_current_debt
FROM startDates AS s 
INNER JOIN balances AS b ON s.Deal = b.Deal;

And the result is:

Deal Num_days_current_debt
111111 4274
122222 17
222221 4266

Upvotes: 2

Related Questions