How to determine columns dynamically for the SELECT query in MySQL with CASE statement. OR: How to replace columns dynamically in the SELECT query

The first step is to create a date range reference table. To do that, we can use Common Table Expression (cte):

WITH RECURSIVE cte AS (
  SELECT Min(mndate) mindt, MAX(mxdate) maxdt 
    FROM (SELECT MIN(date) AS mndate, MAX(date) AS mxdate 
            FROM outcome 
        UNION
          SELECT MIN(date), MAX(date) 
            FROM income) v 
   UNION
  SELECT mindt + INTERVAL 1 DAY, maxdt
   FROM cte 
   WHERE mindt + INTERVAL 1 DAY <= maxdt)
SELECT mindt
   FROM cte

Here I’m trying to generate the dynamic date range based on the minimum & maximum date value from both of your tables. This is particularly useful when you don’t want to keep on changing the date range but if you don’t mind, you can just generate them simply like so:

WITH RECURSIVE cte AS (
  SELECT '2001-03-14 00:00:00' dt 
   UNION
  SELECT dt + INTERVAL 1 DAY
   FROM cte 
   WHERE dt + INTERVAL 1 DAY <= '2002-09-16')
SELECT dt
   FROM cte

From here, I’ll do a CROSS JOIN to get the distinct point value from both tables:

...
   CROSS JOIN (SELECT DISTINCT point FROM outcome 
                 UNION
                SELECT DISTINCT point FROM income) p

Now we have a reference table with all the point and date range. Let’s wrap those in another cte.

WITH RECURSIVE cte AS (
  SELECT Min(mndate) mindt, MAX(mxdate) maxdt 
    FROM (SELECT MIN(date) AS mndate, MAX(date) AS mxdate 
            FROM outcome 
        UNION
          SELECT MIN(date), MAX(date) 
            FROM income) v 
   UNION
  SELECT mindt + INTERVAL 1 DAY, maxdt
   FROM cte 
   WHERE mindt + INTERVAL 1 DAY <= maxdt),
  cte2 AS (
SELECT point, mindt
   FROM cte 
   CROSS JOIN (SELECT DISTINCT point FROM outcome 
                 UNION
                SELECT DISTINCT point FROM income) p)
SELECT * 
  FROM cte2;

Next step is taking your current query attempt and LEFT JOIN it to the reference table:

WITH RECURSIVE cte AS (
  SELECT Min(mndate) mindt, MAX(mxdate) maxdt 
    FROM (SELECT MIN(date) AS mndate, MAX(date) AS mxdate 
            FROM outcome 
        UNION
          SELECT MIN(date), MAX(date) 
            FROM income) v 
   UNION
  SELECT mindt + INTERVAL 1 DAY, maxdt
   FROM cte 
   WHERE mindt + INTERVAL 1 DAY <= maxdt),
  cte2 AS (
SELECT point, CAST(mindt AS DATE) AS rdate
   FROM cte 
   CROSS JOIN (SELECT DISTINCT point FROM outcome 
                 UNION
                SELECT DISTINCT point FROM income) p)
SELECT * 
  FROM cte2
  LEFT JOIN outcome 
     ON cte2.point=outcome.point
    AND cte2.rdate=outcome.date
  LEFT JOIN income 
    ON cte2.point=income.point
    AND cte2.rdate=income.date
/*added conditions*/
WHERE cte2.point=1
AND COALESCE(outcome.date, income.date) IS NOT NULL
/*****/
 ORDER BY cte2.rdate;

I noticed that your date column is using VARCHAR() datatype instead of DATE or DATETIME. Which is why my initial test return only one result. However, I do notice that if I compare YYYY-MM-DD format against your table date value, it returns other results, which is why I did CAST(mindt AS DATE) AS rdate in cte2. I do recommend that you change the date column to MySQL standard date format though.

You probably find the query a bit too long but if you have a table where you store dates or as we call it calendar table, the query will be much shorter, perhaps like this:

SELECT * 
  FROM calendar
  LEFT JOIN outcome 
     ON calendar.point=outcome.point
    AND calendar.rdate=outcome.date
  LEFT JOIN income 
    ON calendar.point=income.point
    AND calendar.rdate=income.date
/*added conditions*/
WHERE calendar.point=1
AND COALESCE(outcome.date, income.date) IS NOT NULL
/*****/
 ORDER BY calendar.rdate;

Demo fiddle