Query to show change planning details with total worklog hours
Working on Build: 14500
MSSQL:
SELECT cdt.changeid "Change ID", cr.rolloutplan "Roll Out Plan", cr.backoutplan "Back Out Plan", cr.checklist "Check List", convert(varchar(5),(sum(ct.TIMESPENT))/1000/3600)+':'+convert(varchar(5),((sum(ct.TIMESPENT))/1000)%3600/60)+':'+convert(varchar(5),
(((sum(ct.TIMESPENT)))/1000%60)) AS "Total work log hours" FROM ChangeDetails cdt LEFT JOIN changeresolution cr ON cdt.changeid = cr.changeid LEFT JOIN ChangeToCharge chtoc ON chtoc.CHANGEID=cdt.CHANGEID LEFT JOIN ChargesTable ct ON ct.CHARGEID=chtoc.CHARGEID WHERE cdt.createdtime>=<from_lastmonth> and cdt.createdtime<=<to_lastmonth> GROUP BY cdt.changeid, cr.rolloutplan, cr.backoutplan, cr.checklist ORDER BY 1
NOTE: Date filter highlighted in the query can be modified and below parameters can be used instead.
How to compare date column with auto filled date templates?
- Here is the example for getting this week data - CREATEDTIME >= <from_thisweek> AND CREATEDTIME <= <to_thisweek>
- <from_thisweek> - Starting date of this week
- <to_thisweek> - Ending date of this week
- Available Date Templates
- Today - <from_today> - <to_today>
- This week - <from_thisweek> - <to_thisweek>
- Last week - <from_lastweek> - <to_lastweek>
- This month - <from_thismonth> - <to_thismonth>
- Last month - <from_lastmonth> - <to_lastmonth>
- This quarter - <from_thisquarter> - <to_thisquarter>
- Last quarter - <from_lastquarter> - <to_lastquarter>
- Yesterday - <from_yesterday> - <to_yesterday>
New to ADSelfService Plus?
Related Articles
Query to show total requests and changes with its total worklog hours of a technician- MSSQL
Working ON Builds: 14500 MSSQL: Requests: SELECT rctd.first_name "Technician", count(wo.workorderid) "Sum of tickets handled by him", ...Query to show total time spent for a ticket ( PGSQL)
Tested in PGSQL build (14300) Database: PGSQL SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", cd.CATEGORYNAME AS "Category", scd.NAME AS "Subcategory", wo.TITLE AS "Subject", rtdef.NAME AS "Request Type", ti.FIRST_NAME AS ...Change Submission and Planning stage details (PGSQL)
Tested in build PGSQL (14300) SELECT cdt.changeid "Change Request ID", orgaaa.FIRST_NAME AS "Change Requester", ownaaa.FIRST_NAME AS "Change Owner", cmDef.FIRST_NAME AS "Change Manager", manager.name "Line Manager", implementer.name "Change ...Query to retrieve worklog details
Tested in: 14610 (Postgres) Query: SELECT wo.WORKORDERID AS "Request ID", aau.FIRST_NAME AS "Requester", sdo.NAME AS "Site", ti.FIRST_NAME AS "Request Technician" ,au1.FIRST_NAME AS "Worklog Technician", TO_CHAR(((sum(ct.TIMESPENT))/1000 || ' ...Query to list the request and its worklogs with the hours spent and its charges (MSSQL)
Tested in build MSSQL (14306) Use case The query will list the request details with the timespent, hours consumed and its charge, Sample Table DB : Poatgres and MSSQL Query SELECT ad.ORG_NAME AS "Account", ac.contractname "Contract Name", ...