with t as ( SELECT cwd_group_par.id as project , cwd_group_par.group_name as project_name , case when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '2015-06-24' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '2015-06-30' then 'LASTWEEK_COUNT' when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '2015-06-01' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '2015-06-30' then 'LASTMONTH_COUNT' when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '2015-07-01'AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '2015-07-07' then 'CURRENTWEEK_COUNT' end as tm , count(distinct i.id) cnt from jira.jiraissue i join jira.cwd_membership cwd_mem on i.assignee = cwd_mem.lower_child_name join jira.cwd_group cwd_group_par on cwd_group_par.lower_group_name = cwd_mem.lower_parent_name join jira.issuestatus on i.issuestatus = jira.issuestatus.id JOIN jira.PROJECT ON i.PROJECT = jira.PROJECT.ID join jira.changegroup chgroup on i.id = chgroup.issueid join jira.changeitem chitem on chgroup.id = chitem.groupid where cwd_group_par.id in("+str(projectids)+" ) and issuestatus in (6 ) AND jira.PROJECT.PKEY = 'ISD' AND chitem.field = 'status' and to_char(chitem.newstring) in ('Closed') group by cwd_group_par.id, cwd_group_par.group_name , case when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '2015-06-24' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '2015-06-30' then 'LASTWEEK_COUNT' when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '2015-06-01' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '2015-06-30' then 'LASTMONTH_COUNT' when TO_CHAR(chgroup.created,'YYYY-MM-DD') >= '2015-07-01' AND TO_CHAR(chgroup.created,'YYYY-MM-DD') <= '2015-07-07' then 'CURRENTWEEK_COUNT' end ) select * from t pivot ( sum(cnt) as wk for tm in ('LASTMONTH_COUNT','LASTWEEK_COUNT','CURRENTWEEK_COUNT') ) order by 1;
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)