Query to display task details and associated account (start date, due date, createdby, assigned to, etc.,)
Use the below query to show all task information (columns listed : taskid, title, Linked account, start date, due date, status, Description, Assigned to, Created by and Created time)
select td.taskid, ad.title, org.name as "Linked Account", longtodate(td.Starttime) as "Start Date", longtodate(td.Duebytime) as "Due Date", astat.Statusname as "Status", ad.description as "Description", aa.First_name as "Assigned to", aal.Name as "Created by", longtodate(ad.Createdtime) from taskdefinition td left join activitydefinition ad on td.Taskid=ad.Activityid left join customer_activity cact on ad.Activityid=cact.Activityid left join aaaorganization org on cact.Customer_id=org.Org_id left join activitystatus astat on td.Statusid=astat.statusid left join aaauser aa on td.Ownerid=aa.User_id left join aaalogin aal on ad.Createdby=aal.User_id
New to ADSelfService Plus?
Related Articles
Apply Contract to All Requests raised by this account option is greyed out while creating a new contract
1. Whenever I put in Expiry date the checkbox for Apply Contract to All Requests raised by this account becomes greyed out. --> This will happen if there is another active contract for the same period. Two rules are applied while creating a contract ...Query to display Accounts and Sub Accounts (8.1)
Execute the below query from the Reports Tab -> New Query (Applicable for 8.1 version) SELECT acc.NAME AS "Account",sorg.NAME AS "Sub Account" FROM Customer cust LEFT JOIN AaaOrganization acc ON cust.CUSTOMER_ID=acc.ORG_ID LEFT JOIN SubAccount ...Login Frequency Query Report
The below report would help us find the last logged in time of the users in SCP 11.0 SELECT AaaUser.FIRST_NAME "Technician", MAX(AaaLogin.NAME) "LoginName", MAX(AaaContactInfo.EMAILID) "Email", MAX(AaaAccSession.USER_HOST) "IP ...Query to get the Request Approval details
Compatible builds : 14000 to 14200 DB : MSSQL /PGSQL OUTPUT: SELECT wo.WORKORDERID AS "Request ID", wo.title AS "Subject", wo.REQUESTERID AS "Requester ID", LONGTODATE(wo.CREATEDTIME) AS "Requested Date", LONGTODATE(apdet.sent_time) AS "Action ...Query to show list of contacts that are assigned to an account but not have a login
The below query shows the list of contacts that are assigned to an account, but does not have a login. SELECT org.NAME AS "Account Name" ,aaauser.FIRST_NAME AS " Contact with no login" FROM AaaUser aaauser LEFT JOIN AaaUserContactInfo user_contact ...