MySQL get list of dates and join with another tab

I’m trying to generate seven rows for each date in the last seven days, and join with a query from transactions table. The aim is to have a table with each date, and the cumulative total of the quantity column in transactions from the first entry up to the date:

|      date     |  stockOnDate  |
|---------------|---------------|
| 2021-10-15    | 10            |
| 2021-10-16    | 3             |
| 2021-10-17    | 0             |
| 2021-10-18    | 9             |
| 2021-10-19    | 15            |
| 2021-10-20    | 15            |
| 2021-10-21    | 15            |

I can get the list of dates, and can join, but can’t filter the nested queries:

SELECT v.*, t.* 
FROM ( SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 + t2*100 + t1*10 + t0)) AS `date` 
       FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3 ) AS v
LEFT JOIN (SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate 
           FROM `transactions` 
           WHERE tDate <= v.`date`) AS t ON t.tDate = v.`date`
WHERE v.`date` >= DATE_SUB(NOW(),INTERVAL 7 DAY) AND v.`date` <= DATE(NOW())

But I’m receviing the following error:

#1054 - Unknown column 'tDate' in 'where clause'

If I replace WHERE tDate <= v.date with WHERE DATE(timestamp) <= v.date I get the same error for v.date – I can’t seem to access the value of the parent tables.

I’m not great with MySQL but can’t seem to find a solution, where am I going wrong?

Contents hide

Solution by ProGuru

Answers:

Method 1

Method 2

Method 3

Solution by ProGuru

Thanks to ProGuru’s answer below, the below query works as expected (using <= instead of = in the JOIN was key)

SELECT b.date, SUM(a.quantity) AS stockOnDate
FROM (
 SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 6 DAY), t3*1000 + t2*100 + t1*10 + t0)) AS `date` 
       FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
) b 
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
AND a.organisationId = 1
GROUP BY b.date
ORDER BY b.date ASC

I can also change the GROUP BY to GROUP BY a.itemID, b.date to get the stock level on the given date for each itemId.

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

To get the cumulative sum of quantity, the date join needs to use <=

Revised SQL

SELECT b.date, SUM(COALESCE(a.quantity, 0))
FROM (
 SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 + t2*100 + t1*10 + t0)) AS `date` 
       FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
) b 
LEFT JOIN transactions a ON DATE(a.timestamp) <= b.date
WHERE b.date BETWEEN DATE(NOW()) - INTERVAL 6 DAY AND DATE(NOW())
GROUP BY b.date
ORDER BY b.date ASC

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=34f78e3d7c9225727ac2e728588759e2

Method 2

To use alias in where clause, you must encapsulate your query in another select.

Example :

SELECT * FROM
    (SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate FROM `transactions`) seb
WHERE seb.tDate <= v.`date`

Update try this:

SELECT v.*, t.* 
FROM ( SELECT DATE(ADDDATE(DATE_SUB(NOW(),INTERVAL 7 DAY), t3*1000 + t2*100 + t1*10 + t0)) AS `date` 
       FROM (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
            (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3 ) AS v
LEFT JOIN (SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate FROM transactions) AS t ON t.tDate = v.`date`
WHERE t.tDate <= v.`date` and v.`date` >= DATE_SUB(NOW(),INTERVAL 7 DAY) AND v.`date` <= DATE(NOW())

here tDate can be used

Method 3

Firstly you need to understand that in this subquery

(SELECT SUM(quantity) AS stockOnDate, DATE(timestamp) as tDate 
           FROM `transactions` 
           WHERE tDate <= v.`date`) AS t

there is nothing defined as date or even the alias v. You’re basically telling the query to look for v.date when it doesn’t exists in the subquery.

I think this is what you’re looking for:

SELECT dt, IFNULL(SUM(quantity),0) AS stockOnDate
    FROM (SELECT STR_TO_DATE(CONCAT(LEFT(NOW(),7),LPAD(seq,2,0)), '%Y-%m%d') dt
             FROM seq_1_to_31) v
        LEFT JOIN  `transactions` t ON dt=DATE(timestamp)
    WHERE dt >= (SELECT MIN(DATE(timestamp)) FROM `transactions`)
       AND dt <= (SELECT MAX(DATE(timestamp)) FROM `transactions`)
GROUP BY dt;

Here I’m using MariaDB’s built-in sequence engine to generate days; concatenated with current year-month combination then uses STR_TO_DATE to make it recognizable as proper date format. The query generating those is in subquery v.

Second option is using a recursive common table expression to generate the date range based on existing date data in the table. Here is the query:

WITH RECURSIVE cte AS (
SELECT MIN(DATE(timestamp)) mndt, MAX(DATE(timestamp)) mxdt FROM `transactions`
UNION ALL
SELECT mndt+INTERVAL 1 DAY, mxdt FROM cte WHERE mndt+INTERVAL 1 DAY <= mxdt)
SELECT mndt, IFNULL(SUM(quantity),0) AS stockOnDate
    FROM cte
        LEFT JOIN  `transactions` t ON mndt=DATE(timestamp)
GROUP BY mndt;

Both the query above eliminates the need to include a long subquery that generates the date range. This is the benefit of newer MySQL & MariaDB versions.

Demo fiddle

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating