If current month query

March 23, 2024

is there any method to change the query below to current month (March 2024), rather than all of 2024

Select
    c.Adviser,
    Count(If(Year(l.LeadDate) = '2024', 1, Null)) As Leads,
    Count(If(Year(l.signedupdate) = '2024', 1, Null)) As Written,
    Count(If(Year(l.CompDate) = '2024', 1, Null)) As Completed
From
    mortga01_portal.tbl_clients c Inner Join
    mortga01_portal.tbl_lead l On l.client_id = c.client_id
Group By
    c.Adviser

Thank you, I’ve tried for hours but getting so confused now.

>Solution :

To change the query to filter for the current month (03/2024), you need to modify the conditions to check for both the year and the month:

SELECT
    c.Adviser,
    COUNT(IF(YEAR(l.LeadDate) = 2024 AND MONTH(l.LeadDate) = 3, 1, NULL)) AS Leads,
    COUNT(IF(YEAR(l.signedupdate) = 2024 AND MONTH(l.signedupdate) = 3, 1, NULL)) AS Written,
    COUNT(IF(YEAR(l.CompDate) = 2024 AND MONTH(l.CompDate) = 3, 1, NULL)) AS Completed
FROM
    mortga01_portal.tbl_clients c
    INNER JOIN mortga01_portal.tbl_lead l ON l.client_id = c.client_id
GROUP BY
    c.Adviser;

Yes, you can modify the query to dynamically select the current year and month. Here’s how you can do it:

SELECT
    c.Adviser,
    COUNT(IF(YEAR(l.LeadDate) = YEAR(NOW()) AND MONTH(l.LeadDate) = MONTH(NOW()), 1, NULL)) AS Leads,
    COUNT(IF(YEAR(l.SignedUpdate) = YEAR(NOW()) AND MONTH(l.SignedUpdate) = MONTH(NOW()), 1, NULL)) AS Written,
    COUNT(IF(YEAR(l.CompDate) = YEAR(NOW()) AND MONTH(l.CompDate) = MONTH(NOW()), 1, NULL)) AS Completed
FROM
    mortga01_portal.tbl_clients c
INNER JOIN
    mortga01_portal.tbl_lead l ON l.client_id = c.client_id
GROUP BY
    c.Adviser;