We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

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

gagan94
Regular Contributor
Regular Contributor

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.

 

 

 

3 REPLIES 3

rushikeshvartak
All-Star
All-Star
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

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

gagan94
Regular Contributor
Regular Contributor

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

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 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.