我需要简化一个复杂的旧查询，以便筛选具有日期范围的is。
我有张table Tickets 以及 TicketNotes .
我需要
列有当天票数的专栏
有票的一栏，有一天的特定音符
日期
旧的查询

SELECT SUM(IFNULL(qtickets.count, 0)) j, SUM(IFNULL(mtickets.count, 0)) m FROM (
    SELECT
        COUNT(tickets.id) COUNT,
        DATE(tickets.date) DATE
    FROM
        tickets
    WHERE
        tickets.status = 'Closed' AND tickets.did = 7 
        AND MONTH(tickets.date) = MONTH( CURRENT_DATE - INTERVAL 1 MONTH ) 
        AND YEAR(tickets.date) = YEAR( CURRENT_DATE - INTERVAL 1 MONTH )
    GROUP BY
        DATE(tickets.date)
) AS mtickets LEFT JOIN (
    SELECT
        1 AS COUNT,
        DATE(tickets.date) DATE
    FROM
        ticketnotes
    INNER JOIN tickets ON tickets.id = ticketnotes.ticketid
    WHERE
        ticketnotes.message LIKE '%https://xxxxx.net/help/tickets/%' 
        AND tickets.status = 'Closed' 
        AND tickets.did = 7 
        AND MONTH(tbltickets.date) = MONTH( CURRENT_DATE - INTERVAL 1 MONTH ) 
        AND YEAR(tbltickets.date) = YEAR( CURRENT_DATE - INTERVAL 1 MONTH )
    GROUP BY
        DATE(tickets.date)
) AS qtickets ON (mtickets.date = qtickets.date)

目标是获得

Date       | M | Q
===================
2020-04-01 | 1 | 1
2020-04-02 | 2 | 1
2020-04-03 | 5 | 2
...
2020-04-30 | 3 | 0

其中m是did=7当天的总关闭票，q是获得note.message的总关闭票。
我需要用date filter date的一个示例检查查询 BETWEEN '2020-04-01' AND '2020-04-30' 仍然得到正确的三列。
=======更新：
当我试图添加 AND DATE(tickets.date) BETWEEN DATE('2020-04-01') AND DATE('2020-04-30') 在gordon的回答中，我从主要查询中得到了其他结果数据。
查询：

SELECT
    DATE(t.date),
    COUNT(t.id) AS num_tickets,
    (CASE WHEN COUNT(tn.ticketid) = 0 THEN 0 ELSE 1 END) AS num_with_message
FROM
    tickets t
LEFT JOIN ticketnotes tn ON
    tn.ticketid = t.id AND tn.message LIKE '%https://xxxxx.net/help/tickets/%' 
WHERE
    t.status = 'Closed' AND t.did = 7 
    AND DATE(t.date) BETWEEN DATE('2020-04-01') AND DATE('2020-04-30')
GROUP BY
    DATE(t.date)

结果是 num_tickets 获取错误数据 num_ticket 没有连接。


有什么建议吗？