Query to show change planning details with total worklog hours

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?
  1. 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
  2. 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 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 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", ...
                    • 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 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 ...
                    • Complete worklog report -MSSQL

                      Query: SELECT aau.FIRST_NAME AS "Requester", max(wo.WORKORDERID) AS "Request ID", rtdef.NAME AS "Request request type", wo.TITLE AS "Subject", wo.description , std.STATUSNAME AS "Request Status", ti.FIRST_NAME AS "Assigned Technician", ...