SQL issue

[Manaralg]

Hi, I’m working on a SQL query that includes a nested select statement. I need to modify it so that the first non-zero value from the right in a "history code" is replaced with a sequence of consecutive numbers, based on certain conditions.

For example, if the history code is 0302230, I want to transform it into 0302232100. In this example, the 3 (which is the first non-zero value from the right) is replaced with 3210, resulting in 03022(3210)0.

The replacement logic depends on conditions involving two dates: paidAt and deadlineDate. Below is the SQL code I’m currently working with:

(
SELECT
GROUP_CONCAT(
CASE
WHEN i.paidAt IS NULL
AND DATE_ADD(i.deadlineDate, INTERVAL 10 DAY) < CURDATE()
AND i.dueDate = (
SELECT MIN(i2.dueDate)
FROM installment i2
WHERE i2.contractId = i.contractId
AND i2.paidAt IS NULL
AND DATE_ADD(i2.deadlineDate, INTERVAL 10 DAY) < CURDATE()
LIMIT 1
)
THEN
CASE
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) <= 0 THEN '0'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 1 AND 29 THEN '1'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 30 AND 59 THEN '2'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 60 AND 89 THEN '3'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 90 AND 119 THEN '4'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 120 AND 149 THEN '5'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 150 AND 179 THEN '6'
WHEN DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) >= 180 THEN 'W'
END
ELSE
CASE
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) <= 0 THEN '0'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 1 AND 29 THEN '1'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 30 AND 59 THEN '2'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 60 AND 89 THEN '3'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 90 AND 119 THEN '4'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 120 AND 149 THEN '5'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) BETWEEN 150 AND 179 THEN '6'
WHEN DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) >= 180 THEN 'W'
END
END ORDER BY i.dueDate DESC SEPARATOR ''
)
FROM installment i
WHERE i.contractId = [c.id](http://c.id/)
) AS 'Payment History Status Code',
)

[lovasoa]

I wrote a small online reproduction to make it easier to collaborate on your problem: https://www.db-fiddle.com/f/w8xD9t2qLgdRv7t51HAaAn/0

Here is a suggestion for a slightly more readable version of the query:

WITH OverdueInstallments AS (
    SELECT 
        i.contractId,
        i.dueDate,
        i.deadlineDate,
        i.paidAt,
        DATEDIFF(CURDATE(), DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) AS daysOverdue,
  		DATEDIFF(i.paidAt, DATE_ADD(i.deadlineDate, INTERVAL 10 DAY)) AS daysPaidAfter
    FROM installment i
),
FirstUnpaidInstallment AS (
    SELECT 
        contractId,
        MIN(dueDate) AS firstDueDate
    FROM OverdueInstallments
    WHERE paidAt IS NULL AND daysOverdue > 0
    GROUP BY contractId
),
StatusCodes AS (
    SELECT 
        o.contractId,
        o.dueDate,
        CASE
            -- For unpaid installments
            WHEN o.paidAt IS NULL AND o.dueDate = f.firstDueDate THEN
                CASE 
                    WHEN o.daysOverdue <= 0 THEN '0'
                    WHEN o.daysOverdue >= 180 THEN 'W'
                    ELSE 1+FLOOR(o.daysOverdue / 30)
                END
            -- For paid installments
            ELSE 
                CASE 
                    WHEN o.daysPaidAfter <= 0 THEN '0'
                    WHEN o.daysPaidAfter >= 180 THEN 'W'
                    ELSE 1+FLOOR(o.daysPaidAfter / 30)
                END
        END AS status
    FROM OverdueInstallments o
    LEFT JOIN FirstUnpaidInstallment f ON o.contractId = f.contractId
)
SELECT 
    c.id AS contractId,
    c.name AS contractName,
    (
        SELECT GROUP_CONCAT(s.status ORDER BY s.dueDate DESC SEPARATOR '')
        FROM StatusCodes s
        WHERE s.contractId = c.id
    ) AS "Payment History Status Code"
FROM contract c;

Concerning the replacements, I'm not sure I understand your question. What replacements do you want to do under which conditions ?

[Manaralg]

transforming the first non-zero occurrence in the history code depending on its value. Here’s a summary of the logic:

If the first non-zero digit from the right is 1, replace it with 10.
If it's 2, replace it with 210.
If it's 3, replace it with 3210, and so on up to:
If it's 6, replace it with 6543210.
If it's W, replace it W6543210.
and append the rest of the history code

[lovasoa]

It's probably better to avoid thinking of this as a string substitution problem. You can add a new CTE between StatusCodes and the final query that returns the same rows as statuscode, but checks for each one if it's the last non-zero dueDate for a given contract and in that case returns the new string of consecutive numbers.

If I may ask, what are you building and in what context do you need this ?