Query to show Timesheet details (MSSQL & PGSQL)
Tested in build PGSQL (14300) and MSSQL (14306)
MSSQL:
select ts.id "TimeSheet ID", sdu.firstname "Technician Name", LONGTODATE(ts.starttime) "Timesheet Start Date", LONGTODATE(ts.endtime) "Timesheet End Date", CONVERT(varchar, ((ts.totaltime)/1000) / 3600) + ':' + RIGHT('0' + CONVERT(varchar,((ts.totaltime)/1000) % 3600 / 60), 2) + ':' + RIGHT('0' + CONVERT(varchar, ((ts.totaltime)/1000) % 60), 2)
"Total Time Spent", tsd.name "Approve Timesheet Status" from Timesheet ts LEFT JOIN timesheetstatusdefinition tsd ON ts.statusid=tsd.id LEFT JOIN SDUser sdu ON ts.technicianid=sdu.userid
PGSQL:
select ts.id "TimeSheet ID", sdu.firstname "Technician Name", LONGTODATE(ts.starttime) "Timesheet Start Date", LONGTODATE(ts.endtime) "Timesheet End Date",cast((ts.Totaltime)/1000 * '1 second'::interval as varchar) "Total Time Spent", tsd.name "Approve Timesheet Status" from Timesheet ts LEFT JOIN timesheetstatusdefinition tsd ON ts.statusid=tsd.id LEFT JOIN SDUser sdu ON ts.technicianid=sdu.userid
New to ADSelfService Plus?
Related Articles
Query to show Problem - Task Report with Problem Details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Attached the sample query output. Query: SELECT prob.PROBLEMID AS "Problem ID", prob.TITLE AS "Title",catadef.CATEGORYNAME AS "Category", longtodate(prob.CLOSEDTIME) AS "Closed Date", ...Query report to get the successfully scanned assets (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Query report to get the successfully scanned assets with audit status, state , Serial no and site. SELECT max(resource.resourcename) "Asset Name", max(resource.SERIALNO) AS "Org Serial Number", ...Contract and Service Plans details - Query Report (MSSQL & PGSQL)
Tested in Build PGSQL (14300) or MSSQL (14306) PGSQL & MSSQL: select ac.contractno "Contract Number", ac.contractname "Contract Name", sp.serviceplanname "Service Plan Name",sp.plantype "Service Plan Type", sp.timeperiod "Bill ...Query to get the login failed attempt details (MSSQL & PGSQL)
Tested in builds PGSQL (14300) or MSSQL (14306) Go to Reports tab > New Query Report, use the below query to get the login failed attempts details, select principal "User Name", LONGTODATE(timestamp), HOSTNAME, OPERATIONNAME from AuditRecord ar LEFT ...Query to show purchase order full details (PGSQL)
Tested in PGSQL build (14300) SELECT po.POCUSTOMID AS "Order No.",poi.serialno "S.No", poaddfield.UDF_CHAR1 AS "Legal Entity", po.PONAME AS "PO Name", pos.STATUSNAME AS "PO Status", cy.currencyname "Currency", paao.NAME AS "Vendor Name", po.SHIPTO AS ...