dorkness-io

with cte as 
  ( 
    select
      ss.pname
      ,ss.time_worked
      ,rank() over (order by ss.time_worked desc) project_rank
    from
    (
      select
        p.pname

dorkness-io

-- Create a special schema to store our logging data
create schema logging;

-- Create a new table within the schema to log our events
create table logging.events 
  (
    id serial
    ,timestamp timestamptz default current_timestamp
    ,schema_name text
    ,table_name text

dorkness-io

select
  p.pkey || '-' || ji.issuenum AS issue_key
  ,cs.start_date
from jiraissue ji
join project p
  on ji.project = p.id
join os_currentstep cs
  on ji.workflow_id = cs.entry_id

union

dorkness-io

select
  ss.pname
  ,sum(ss.comment_count) as sum_comments
from (
select
  p.pname
  ,p.pkey || '-' || ji.issuenum AS issue_key
  ,count(distinct ja.id) as comment_count
from jiraissue ji
join project p

dorkness-io

select
  p.pkey || '-' || ji.issuenum AS issue_key
from jiraissue ji
join project p
  on ji.project = p.id
join jiraaction ja
  on ji.id = ja.issueid
  and actiontype = 'comment'
group by 1
having count(ja.id) >= 10;

dorkness-io

drop table if exists weekly_issues;

select 
  p.pkey || '-' || ji.issuenum AS issue_key
  ,ji.summary
  ,ji.reporter
  ,ji.created
into temp weekly_issues
from jiraissue ji
join project p

dorkness-io

select
  p.pkey || '-' || ji.issuenum AS issue_key
  ,ja.actionbody
from jiraissue ji
inner join project p
  on ji.project = p.id
left join jiraaction ja
  on ji.id = ja.issueid
  and actiontype = 'comment'
where p.pkey = 'TP';

dorkness-io

select
  p.pkey || '-' || ji.issuenum AS issue_key
  ,ja.actionbody
from jiraissue ji
inner join project p
  on ji.project = p.id
inner join jiraaction ja
  on ji.id = ja.issueid
  and actiontype = 'comment'
where p.pkey = 'TP';

dorkness-io

select
  p.pkey || '-' || ji.issuenum AS issue_key
  ,ja.actionbody
from jiraaction ja
right join jiraissue ji
  on ja.issueid = ji.id
join project p
  on p.id = ji.project
where p.pkey = 'TP';

dorkness-io

select
  p.pkey || '-' || ji.issuenum AS issue_key
  ,ja.actionbody
from jiraaction ja
full outer join jiraissue ji
  on ja.issueid = ji.id
join project p
  on p.id = ji.project
where p.pkey = 'TP';