|
I am using a query that sorts transition in jira events to dispatch time by category.
When a subquery is giving :
select tkt.id as issueid
|
, case when right(g.created,8)>='18:00:00' then concat(date(g.created),' 18:00:00') when right(g.created,8)<'09' then concat(date(g.created),' 09:00:00') else g.created end as c2
|
,i.newstring,i.oldstring
|
, case when right(tkt.created,8)>='18:00:00' then concat(date(tkt.created),' 18:00:00') when right(tkt.created,8)<'09' then concat(date(tkt.created),' 09:00:00') else tkt.created end as c1
|
,r.who
|
from
|
changegroup g
|
inner join (select distinct j.id,j.CREATED
|
from changegroup g
|
inner join changeitem i on i.groupid=g.id
|
inner join jiraissue j on j.id=g.issueid
|
inner join project p on j.project=p.ID
|
inner join issuetype t on t.ID=j.issuetype
|
where g.CREATED between '2019-11-01' and '2019-11-30 23:59:59.9'
|
and i.fieldtype='jira' and i.FIELD='status' and ((i.newstring = 'Resolved' and i.OLDSTRING != 'Closed') or (i.newstring = 'Closed' and i.OLDSTRING != 'Resolved'))
|
and p.pname in ('long list of projects')
|
and j.issuetype in (10000,10001,4,7,8)
|
and j.id=33698
|
) tkt on g.issueid=tkt.id
|
inner join changeitem i on i.groupid=g.id and i.fieldtype='jira' and i.FIELD='status'
|
inner join respsonsabilities r on r.status=i.oldstring
|
where g.CREATED <= '2019-11-30 23:59:59.9'
|
order by g.issueid,case when right(g.created,8)>='18:00:00' then concat(date(g.created),' 18:00:00') when right(g.created,8)<'09' then concat(date(g.created),' 09:00:00') else g.created end
|
wich results in
+---------+---------------------+--------------+--------------+---------------------+------+
|
| issueid | c2 | newstring | oldstring | c1 | who |
|
| 33698 | 2019-10-01 18:00:00 | Validated | Open | 2019-10-01 17:21:00 | KU |
|
| 33698 | 2019-10-02 09:49:05 | In Progress | Validated | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-10-02 10:25:54 | Need details | In Progress | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-10-02 17:59:52 | Unblocked | Need details | 2019-10-01 17:21:00 | KU |
|
| 33698 | 2019-10-02 18:00:00 | In Progress | Unblocked | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-10-03 11:27:42 | Need details | In Progress | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-10-30 15:26:48 | Unblocked | Need details | 2019-10-01 17:21:00 | KU |
|
| 33698 | 2019-10-30 16:14:43 | In Progress | Unblocked | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-10-30 18:00:00 | Need details | In Progress | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-11-04 16:30:54 | Unblocked | Need details | 2019-10-01 17:21:00 | KU |
|
| 33698 | 2019-11-04 16:31:08 | In Progress | Unblocked | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-11-04 17:19:28 | Resolved | In Progress | 2019-10-01 17:21:00 | CCA |
|
| 33698 | 2019-11-06 09:00:00 | Closed | Resolved | 2019-10-01 17:21:00 | KU |
|
+---------+---------------------+--------------+--------------+---------------------+------+
|
|
I am expecting the top query around it to use the records in the order provided by the subquery, but it does use a different sorting.
The wraping query :
select issueid,case when OLDSTRING='Open' then c1 else @prev_date end as start_date
|
,@prev_date:= c2 as end_date
|
,c2,c1,oldstring,newstring,who
|
from (
|
select tkt.id as issueid
|
, case when right(g.created,8)>='18:00:00' then concat(date(g.created),' 18:00:00') when right(g.created,8)<'09' then concat(date(g.created),' 09:00:00') else g.created end as c2
|
,i.newstring,i.oldstring
|
, case when right(tkt.created,8)>='18:00:00' then concat(date(tkt.created),' 18:00:00') when right(tkt.created,8)<'09' then concat(date(tkt.created),' 09:00:00') else tkt.created end as c1
|
,r.who
|
from
|
changegroup g
|
inner join (select distinct j.id,j.CREATED
|
from changegroup g
|
inner join changeitem i on i.groupid=g.id
|
inner join jiraissue j on j.id=g.issueid
|
inner join project p on j.project=p.ID
|
inner join issuetype t on t.ID=j.issuetype
|
where g.CREATED between '2019-11-01' and '2019-11-30 23:59:59.9'
|
and i.fieldtype='jira' and i.FIELD='status' and ((i.newstring = 'Resolved' and i.OLDSTRING != 'Closed') or (i.newstring = 'Closed' and i.OLDSTRING != 'Resolved'))
|
and p.pname in ('long list of projects')
|
and j.issuetype in (10000,10001,4,7,8)
|
and j.id=33698
|
) tkt on g.issueid=tkt.id
|
inner join changeitem i on i.groupid=g.id and i.fieldtype='jira' and i.FIELD='status'
|
inner join respsonsabilities r on r.status=i.oldstring
|
where g.CREATED <= '2019-11-30 23:59:59.9'
|
order by g.issueid,case when right(g.created,8)>='18:00:00' then concat(date(g.created),' 18:00:00') when right(g.created,8)<'09' then concat(date(g.created),' 09:00:00') else g.created end
|
) base
|
|
is returning something sorted by an unpredictable way and the condition initialising and updating the @prev_date does not work.
+---------+---------------------+---------------------+---------------------+---------------------+--------------+--------------+------+
|
| issueid | start_date | end_date | c2 | c1 | oldstring | newstring | who |
|
| 33698 | 2019-10-01 17:21:00 | 2019-10-01 18:00:00 | 2019-10-01 18:00:00 | 2019-10-01 17:21:00 | Open | Validated | KU |
|
| 33698 | 2019-10-01 18:00:00 | 2019-10-02 10:25:54 | 2019-10-02 10:25:54 | 2019-10-01 17:21:00 | In Progress | Need details | CCA |
|
| 33698 | 2019-10-02 10:25:54 | 2019-10-03 11:27:42 | 2019-10-03 11:27:42 | 2019-10-01 17:21:00 | In Progress | Need details | CCA |
|
| 33698 | 2019-10-03 11:27:42 | 2019-10-30 18:00:00 | 2019-10-30 18:00:00 | 2019-10-01 17:21:00 | In Progress | Need details | CCA |
|
| 33698 | 2019-10-30 18:00:00 | 2019-11-04 17:19:28 | 2019-11-04 17:19:28 | 2019-10-01 17:21:00 | In Progress | Resolved | CCA |
|
| 33698 | 2019-11-04 17:19:28 | 2019-10-02 17:59:52 | 2019-10-02 17:59:52 | 2019-10-01 17:21:00 | Need details | Unblocked | KU |
|
| 33698 | 2019-10-02 17:59:52 | 2019-10-30 15:26:48 | 2019-10-30 15:26:48 | 2019-10-01 17:21:00 | Need details | Unblocked | KU |
|
| 33698 | 2019-10-30 15:26:48 | 2019-11-04 16:30:54 | 2019-11-04 16:30:54 | 2019-10-01 17:21:00 | Need details | Unblocked | KU |
|
| 33698 | 2019-11-04 16:30:54 | 2019-10-02 09:49:05 | 2019-10-02 09:49:05 | 2019-10-01 17:21:00 | Validated | In Progress | CCA |
|
| 33698 | 2019-10-02 09:49:05 | 2019-10-02 18:00:00 | 2019-10-02 18:00:00 | 2019-10-01 17:21:00 | Unblocked | In Progress | CCA |
|
| 33698 | 2019-10-02 18:00:00 | 2019-10-30 16:14:43 | 2019-10-30 16:14:43 | 2019-10-01 17:21:00 | Unblocked | In Progress | CCA |
|
| 33698 | 2019-10-30 16:14:43 | 2019-11-04 16:31:08 | 2019-11-04 16:31:08 | 2019-10-01 17:21:00 | Unblocked | In Progress | CCA |
|
| 33698 | 2019-11-04 16:31:08 | 2019-11-06 09:00:00 | 2019-11-06 09:00:00 | 2019-10-01 17:21:00 | Resolved | Closed | KU |
|
+---------+---------------------+---------------------+---------------------+---------------------+--------------+--------------+------+
|
I tried to sort the wraping query but the sorting occurs after the computations and it does not work.
|