How to search issues by their historical status?

Hi, everybody.

If I want to know an issue's status of yesterday. I can do this:

    detail = JournalDetail.
        joins(:journal => :issue).
        where(:property => 'attr', :prop_key => 'status_id').
        where("#{Issue.table_name}.id" => issue.id).
        where("#{Journal.table_name}.created_on <= ?", Date.yesterday.to_time.end_of_day).
        order("#{Journal.table_name}.created_on DESC").first
    if detail
      status = IssueStatus.find(detail.value)
    else
      status = issue.status
    end

But, I want to know how many issues were opened, and how many were closed at yesterday.
I don't want to count it one by one.
any one can help me?

Replies (4)

RE: How to search issues by their historical status? - Added by Martin Denizet (redmine.org team member) about 10 years ago

Hi Haihan,

I suggest:

issues_closed_yesterday_count = Issue.where('closed_on > ? AND closed_on < ?', 
Date.yesterday.to_time, Date.yesterday.to_time.end_of_day).count

issues_opened_yesterday_count = Issue.where('created_on > ? AND created_on < ?', 
Date.yesterday.to_time, Date.yesterday.to_time.end_of_day).count

issues_opened_and_not_closed_yesterday_count = Issue.where('(created_on > ? AND created_on < ?) AND NOT(closed_on > ? AND closed_on < ?)', 
Date.yesterday.to_time, Date.yesterday.to_time.end_of_day,  Date.yesterday.to_time, Date.yesterday.to_time.end_of_day).count

issues_opened_yesterday_and_still_opened_count = Issue.where('created_on > ? AND created_on < ?', 
Date.yesterday.to_time, Date.yesterday.to_time.end_of_day).open(true).count

Note that I didn't test this code, there could be mistakes.

Cheers,

RE: How to search issues by their historical status? - Added by Haihan Ji about 10 years ago

Hi, martin.
Thanks for your suggestion.
But it's diffrent.

The first is "How many issues were closed at yesterday". But if the issue is re-opened , then re-closed at today. My count will miss this one.
The 2nd, 3rd, 4th will miss the issues were created at the day before yesterday.

So, I have to say it's not a correct answer.

Until now, I have to retrieve all issues with all journal-details, and count them one by one.
( The requirement is Drawing-Sprint-Burn-Down-Chart)

For example

	Mon	Tue	Wed	Thu	Fri
# 1	Created	(NC)	Closed	(NC)	(NC)
# 2	Created	(NC)	Closed	Feedback	Closed
# 3	-	Created	Closed	(NC)	(NC)
# 4	Created	(NC)	(NC)	Closed	Feedback, Re-Closed

NC = No Change

Result should be:

	M	T	W	T	F
Open	3	4	1	1	0
Closed	0	0	3	3	4
Total	3	4	4	4	4

I think a complex SQL maybe work out, but My SQL-Skill is not enough.

RE: How to search issues by their historical status? - Added by Matt Wiseley about 10 years ago

I'm new enough to Rails that its easier for me to express this in straight up SQL. Hopefully this is helpful to you. This would give you a list of dates with the number of issues closed that day:

SELECT LEFT(journals.created_on, 10) AS 'Date', COUNT(DISTINCT issues.id) AS 'Closed' FROM issues
INNER JOIN journals ON issues.id=journals.journalized_id AND journals.journalized_type='Issue'
INNER JOIN journal_details ON journals.id=journal_details.journal_id AND journal_details.property='attr' AND journal_details.prop_key='status_id'
WHERE journal_details.value=5
GROUP BY Date;

Note that on the last line you'd want to replace =5 with IN (5,6,7) if you had additional statuses that were considered "Closed" with IDs of 6 and 7.

To count Open issues is a bit trickier, as you'd have to include all the non-closed status IDs in the last line using NOT IN(1,2,3) and you'd have to UNION that with a count of IDs in the Issues table using created_on, by adding this above the "GROUP BY" line:

UNION
SELECT LEFT(created_on, 10) AS 'Date', COUNT(*) FROM issues

Now, having written all that, I think I may have misunderstood your goal. "Historical status" is different from what is stored in the Journal tables. They only store what changed - not a snapshot of everything on a certain date. So, the data to list every issue that was open (not actually opened, but just sitting open) on a certain historical date simply isn't stored in the redmine database. All they can report on is what changed on a certain date. If that's what you're looking for, hopefully my examples are of some help.

RE: How to search issues by their historical status? - Added by Konstantin Tkachenko almost 10 years ago

The backlogs plugin has solved that issue by patching the Issue class and maintaining additional History for the changes of the important properties on issues.

Simply using "plain" SQL without programming it would be pretty tricky to write such an sql (not to mention how it would affect performance of the productive server, if some more users start to request such reports). I have the similar issue and I'm planning to fix it by modifying one of the used plugins (we need cumulative flow diagram for our kanban).

The history table could look like this:
status_history (id, issue_id, status_id, from_date not null, to_date null).

(1-4/4)

Project

General

Profile

Redmine