Count of Completed Task of Last Month of Add, remove Tasks type

[gagan94]

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.

[rushikeshvartak]

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

[gagan94]

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

[rushikeshvartak · Accepted solution]

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