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>