The query I've been using right now looks like this:
SELECT i.issuenum, substr(i.summary,30) as COMPONENT, cg.ID, cg.issueid, au.lower_user_name, cg.AUTHOR, to_date(SUBSTR(cg.created,1,10)) as CREATEDATE, ci.FIELD, ci.OLDVALUE, ci.NEWVALUE, (to_char(ci.Newvalue)-(NVL(to_char(ci.oldvalue),0)))/3600 AS LOGGEDHOUR
FROM alm_jira_repos.changegroup cg
INNER JOIN alm_jira_repos.jiraissue i on cg.issueid = i.id INNER JOIN alm_jira_repos.changeitem ci on ci.groupid = cg.id AND ci.FIELD='timespent' INNER JOIN alm_jira_repos.app_user au on cg.author = au.user_key
The total of logged hours is OK, but I would need to split them into weeks. The problem is that cg.created field shows the date when the timelogging was made, but not the date, onto which time was logged (hope it makes any sense). Could you please help, where could I find the needed field in JIRA DB? |