Query to show change planning details with total worklog hours

Query to show change planning details with total worklog hours

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?

        Resources

            • Related Articles

            • Query to show total requests and changes with its total worklog hours of a technician _ MSSQL

              MSSQL: Requests: SELECT rctd.first_name "Technician", count(wo.workorderid) "Sum of tickets handled by him", convert(varchar(5),(sum(ct.TIMESPENT))/1000/3600)+':'+convert(varchar(5),((sum(ct.TIMESPENT))/1000)%3600/60)+':'+convert(varchar(5), ...
            • Change Submission and Planning stage details

              PGSQL: 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 Implementer", Reviewer.name ...
            • Query to show worklogs under change tasks

              PGSQL: SELECT ad.ORG_NAME AS "Account", tk.taskid "Task ID", COALESCE(ctk.CHANGEID , ch.CHANGEID) "Change ID", to_timestamp((ct.createdtime)/1000)::TIMESTAMP "Time Spent Created Time",'Change' "Module",cast((sum(ct.TIMESPENT)/1000 * interval '1 ...
            • Query to show total time spent for a ticket

              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 "Technician", sdo.NAME AS "Site", ...
            • Query to show the last worklog added in a ticket

              PGSQL: SELECT wo.WORKORDERID "Request ID",        max(aau.FIRST_NAME) "Requester",        max(wo.TITLE) "Subject",        max(qd.QUEUENAME) "Group",        max(ti.FIRST_NAME) "Assigned Technician",        MAX(cast((ct.TIMESPENT)/1000 * interval '1 ...