I have written the following code for Monthly status report. But I am getting the reports from 30 sept to 30 oct. I want the results from 1oct to 31oct. What changes must be done??
SELECT DISTINCT
d.reports_to_name AS “Manager Name:Data:190”,
d.employee_name AS “Employee Name:Data:150”,
d.date AS “DSR Date:Date:120”,
(CASE WHEN d.holiday = 1 THEN ‘Holiday’ WHEN d.dsr_pause = 1 THEN ‘On Leave’ WHEN d.dsr_created_on IS NULL THEN ‘Missed DSR’ WHEN d.date = d.dsr_created_on THEN ‘On Time’ ELSE ‘Delayed’ END) AS ‘DSR Details:Data:120’,
DATEDIFF(d.dsr_created_on,d.date) AS “Delayed By:Data:100”,
d.todays_tasks AS ‘Tasks List:Text:500’
FROM
tabCalendar DSR as d
WHERE
DATEDIFF(now(),d.date) BETWEEN 1 AND 31
ORDER BY d.reports_to_name, d.employee_name, d.date ASC