How can I get a report showing the submission date and the editorial decision date (the first one at least)?

Software Support
,
1

I need to calculate the time between submission and an initial decision in an artichle. How can I retrived those two dates?

2

Do you mean that you want to calculate it automatically? If not, you can see the dates from the editorial history. https://pkp.gitbooks.io/ojs3/content/en/submissions.html

3

Yes, I need something to do it automatically. I can run a SQL query if needed, but I honestely don’t know where to start to get that from OJS.

BTW, I’m not using 3.0.

4

Well date_submitted is easy: ojs/ojs_schema.xml at ojs-stable-2_4_8 · pkp/ojs · GitHub
And the decision dates you can get from ojs/ojs_schema.xml at ojs-stable-2_4_8 · pkp/ojs · GitHub
You can limit the type of decisions with this ojs/ojs_schema.xml at ojs-stable-2_4_8 · pkp/ojs · GitHub

So basically you could write an SQL like SELECT articles.article_id, articles.date_submitted, edit_decisions.date_decided, edit_decisions.decision DATEDIFF(articles.date_submitted, edit_decisions.date_decided) FROM articles LEFT JOIN edit_decisions ON articles.article_id = edit_decisions.article_id; (did not test this)

5

Thanks, with your help I got it working. This is my query (columns named in portuguese):

SELECT id, s.setting_value AS título, data_submissão, data_decisão, DATEDIFF( data_decisão, data_submissão ) AS dias_passados, decisão
FROM (
	SELECT a.article_id AS id, a.date_submitted AS data_submissão, MIN( e.date_decided ) AS data_decisão, e.decision AS decisão
	FROM articles a
	INNER JOIN edit_decisions e ON a.article_id = e.article_id
	WHERE a.journal_id =56
	GROUP BY a.article_id
) tbl
LEFT JOIN article_settings s ON tbl.id = s.article_id
	AND s.setting_name =  "title"
	AND locale =  "pt_BR"
ORDER BY data_decisão DESC
1 Like