Request status query for 'Request Completed, Task pending'

[RV]

I am looking for a Analytical Query that displays the Request status similar to what 'Request History' shows with status :  'Request Completed, Tasks pending' or 'Request Completed, Tasks completed'. 

I did not find any column in ars_requests that displays these values.  Can someone provide me a table or query ?

[RV]

Also, date/time on when all of the associated Tasks on a Request are completed.  

[rushikeshvartak · Accepted solution]

select SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) 'REQUEST ID',at.taskkey,at.accountname,(CASE at.status WHEN 1 THEN 'New' WHEN 2 THEN 'In Progress' WHEN 3 THEN 'Completed' WHEN 4 THEN 'Discontinued' WHEN 8 THEN 'Error' WHEN 9 THEN 'No Action Required' END) AS 'TASK STATUS',(CASE WHEN ar.status = 1 THEN 'Open' WHEN ar.STATUS = 2 THEN 'In Process' WHEN ar.STATUS = 3 THEN 'Completed' WHEN ar.status = 4 THEN 'Expired' ELSE 'Discontinued' END) AS 'REQUEST STATUS',(CASE WHEN AR.status IN (3 , 4 ) THEN MAX(AT.TASKDATE) ELSE '' END) AS 'REQUEST COMPLETION DAT' from arstasks at ,ars_requests ar where at.requestkey=ar.requestkey group by at.requestkey