12/16/2022 09:10 AM
Hi Everyone,
I need to have list of completed tasks from last month of add and remove type arstasks table,
SELECT count(*) FROM arstasks a where a.status = 3 AND a.tasktype IN ( 1, 2 ) and datediff(current_date(),a.taskdate) <= 30
Can anybody Suggest whether the query written is wrong? because I am getting more counts but different one with below query
SELECT count(*) FROM arstasks a where a.status = 3 AND a.tasktype IN ( 1, 2 ) and a.taskdate like '2022-11%'
Ideally the count should get matched but getting different values.
Solved! Go to Solution.
12/16/2022 09:26 AM
SELECT case a.tasktype when 1 then 'Add' when 2 then 'Remove' end as tasktype, count(*) FROM arstasks a where a.status = 3 AND a.tasktype IN ( 1, 2 ) and datediff(current_date(),a.taskdate) <= 30 group by a.status,a.tasktype
12/16/2022 11:01 AM
No its not working for me actually count is same what I was getting.
I want the count from last month, It should be able to get count from 1st nov-30 nov if I am running it on december 1, even I am running on 17th it should get count from completed task from 1st November to 30th November 2022.
Regards,
Gagan
12/16/2022 11:34 AM
SELECT CASE A.TASKTYPE
WHEN 1 THEN 'Add'
WHEN 2 THEN 'Remove'
END AS tasktype,
COUNT(*)
FROM ARSTASKS A
WHERE A.STATUS = 3
AND A.TASKTYPE IN ( 1, 2 )
AND TASKDATE BETWEEN DATE_FORMAT(NOW() - INTERVAL 1 MONTH,
'%Y-%m-01 00:00:00') AND
DATE_FORMAT(LAST_DAY(NOW() - INTERVAL 1 MONTH),
'%Y-%m-%d 23:59:59')
GROUP BY A.STATUS,
A.TASKTYPE