How do I return a dynamically-created string in MySQL that contains 1 to N values?

Question

Let me first explain what I'm trying to do. I have a few tables that contain customer data. I'm trying to write a query that for certain customers will use last payment date (lstpaid), pay cycle (refi_max_daysdelin), and the current date to return a column that is a string that shows how many payments they have missed with the payment amount (payamt) after each date. It's easy enough to query all of the individual fields, but creating the string is where I am running into issues.

So for example let's say the current date is 05/07/14 (because it is), and let's say a customer's pay amount is 150.00. If their last payment date was 04/01/14 and their pay cycle is 14 days, they have missed 2 payments. So the string should return "04/15/14,150.00,04/29/14,150.00".

A coworker of mine got it working using CASE statements, but the problem with this method is that it is not dynamic. He had to write individual CASE statements into the MySQL to handle each new date / payamt added to the string. He went up to 15 CASES (x2 = 30 to get both date and payamt), the query was huge and bloated, and even then it's possible that we might need more. Basically it needs to be dynamic.

I just stripped it down to 2 cases the example SQL I'm posting here to make it easier to understand.

It seems like it should be simple enough to make this more dynamic, but I'm not sure how to proceed. In PHP I would just use a WHILE loop. Is that the best method in MySQL? I don't have any real experience with loops in MySQL. My understanding is that to use loops in MySQL requires creating stored procedures, is that correct? I would rather avoid that route if possible. But if that is the only way, a point in the right direction there would be appreciated.

Here is the SQL query with the CASE statements. As I said, it works, it's just not very feasible if you want it to handle large date ranges:

SELECT DISTINCT xyzcustunq.socsec, xyzacct.name, xyzacct.opendt AS agreementdate, xyzacct.payamt, 
CONCAT_WS(',',duedate1,paymentamt1,duedate2,paymentamt2) AS duedates

FROM xyzcustunq 
INNER JOIN xyzacct ON (xyzcustunq.socsec=xyzacct.socsec) 
INNER JOIN xyzclass ON (xyzacct.class=xyzclass.class)

LEFT OUTER JOIN 
(
    SELECT xyzacct.socsec AS zsocsec,

    CASE when (xyzacct.lstpaid NOT IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.lstpaid, INTERVAL +(xyzclass.refi_max_daysdelin) DAY) <= CURDATE()))
    then (DATE_ADD(xyzacct.lstpaid, INTERVAL +(xyzclass.refi_max_daysdelin) DAY))
    when (xyzacct.lstpaid IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.opendt, INTERVAL +(xyzclass.refi_max_daysdelin) DAY) <= CURDATE())) 
    then (DATE_ADD(xyzacct.opendt, INTERVAL +(xyzclass.refi_max_daysdelin) DAY))
    else NULL end 
    AS duedate1,

    CASE when (xyzacct.lstpaid NOT IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.lstpaid, INTERVAL +(xyzclass.refi_max_daysdelin) DAY) <= CURDATE()))
    then xyzacct.payamt
    when (xyzacct.lstpaid IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.opendt, INTERVAL +(xyzclass.refi_max_daysdelin) DAY) <= CURDATE())) 
    then xyzacct.payamt
    else NULL end 
    AS paymentamt1,

    CASE when (xyzacct.lstpaid NOT IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.lstpaid, INTERVAL +(xyzclass.refi_max_daysdelin*2) DAY) <= CURDATE())) 
    then (DATE_ADD(xyzacct.lstpaid, INTERVAL +(xyzclass.refi_max_daysdelin*2) DAY))
    when (xyzacct.lstpaid IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.opendt, INTERVAL +(xyzclass.refi_max_daysdelin*2) DAY) <= CURDATE())) 
    then (DATE_ADD(xyzacct.opendt, INTERVAL +(xyzclass.refi_max_daysdelin*2) DAY))
    else NULL end 
    AS duedate2,

    CASE when (xyzacct.lstpaid NOT IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.lstpaid, INTERVAL +(xyzclass.refi_max_daysdelin*2) DAY) <= CURDATE()))
    then xyzacct.payamt
    when (xyzacct.lstpaid IN ('00-00-0000', '00/00/0000') AND 
    (DATE_ADD(xyzacct.opendt, INTERVAL +(xyzclass.refi_max_daysdelin*2) DAY) <= CURDATE())) 
    then xyzacct.payamt
    else NULL end 
    AS paymentamt2

    FROM xyzcustunq 
   INNER JOIN xyzacct ON (xyzcustunq.socsec=xyzacct.socsec) 
   INNER JOIN xyzclass ON (xyzacct.class=xyzclass.class)
    WHERE 
    (xyzacct.pstatus = 'A')

    GROUP BY xyzacct.socsec
) z ON (xyzacct.socsec = z.zsocsec)  

WHERE 
(xyzacct.pstatus = 'A') AND
(xyzacct.curbal > 0.00) AND
(xyzacct.socsec LIKE '%490%')

ORDER BY xyzacct.name

And here is an example of the output:

Customer Socsec Customer Name 02/28/2014 117.00 2014-04-11,117.00,2014-04-25,117.00

http://stackoverflow.com/a/12599372/1745672 – Mihai May 07 '14 at 20:56 — Mihai, May 07 '14 at 20:56

Loek Bergman · Answer 1 · 2014-05-07T21:35:53.333

I would create the query in MySQL and process the results in PHP.

PHP is faster with loops, MySQL faster in querying a lot of data.

The result of MySQL should be a list of all possible paydates in one variable and the paid amount on that date done by the customer. That requires a left outer join. Then you process each row in PHP and create the string.

The query is basically something like this:

select abc.paydate, cust.paydate, cust.amount from paydates abc left join customerpayings     
cust on abc.paydate=cust.paydate;

With this query will you also be able to find missing payments before the last complete payment.

If you would need to create the loop in MySQL I would definitely write a stored procedure and use a cursor. They are made for this purpose. A great advantage of stored procedures is that they are precompiled and therefor a good defense against SQL injection attacks.

Processing in PHP would be a piece of cake. However, without going into too much detail, the people I am doing this for have a report generator which allows them to create MySQL statements to query their data with, and they don't have access to PHP, nor do they know how to write PHP anyway. I'd prefer a pure MySQL solution. — Andrew Whatever, May 07 '14 at 21:34

How do I return a dynamically-created string in MySQL that contains 1 to N values?

1 Answers1