Leads created in a particular month and month in which the lead was converted in tabular format.
SELECT
lead.name AS “Lead Name”,
DATE_FORMAT(lead.creation, ‘%Y-%m’) AS “Created Month”,
DATE_FORMAT(lead.converted_on, ‘%Y-%m’) AS “Converted Month”
FROM
tabLead AS lead
WHERE
lead.creation BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
AND lead.converted_on IS NOT NULL
Facing error in this: ValueError: unsupported format character ‘Y’ (0x59) at index 71
Please apply it.
SELECT
lead.name AS `Lead Name:Link/Lead:200`,
CONCAT(YEAR(lead.creation), '-', LPAD(MONTH(lead.creation), 2, '0')) AS `Created Month`,
CONCAT(YEAR(lead.converted_on), '-', LPAD(MONTH(lead.converted_on), 2, '0')) AS `Converted Month`
FROM
`tabLead` AS lead
WHERE
lead.creation BETWEEN DATE_SUB(CURDATE(), INTERVAL 12 MONTH) AND CURDATE()
AND lead.converted_on IS NOT NULL
Output:
I don’t have a converted_on field so I haven’t added it to the report. Now set your logic according to the scenario.
