I need to calculate the time between submission and an initial decision in an artichle. How can I retrived those two dates?
How can I get a report showing the submission date and the editorial decision date (the first one at least)?
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
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.
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)
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