Select count query based based on multiple records in table

Databases
1

I’ve got a table with lot of records. These records contain a project history.
I tried several forums and no one knows the answer.
So table as follows
Id | Projectid | timestamp | status | interventionId.

Now during a project, the status becomes to terminated on a certain timestamp.
When it is terminated, it can be reopened. So the status becomes reopened.

What I want to have is the number of projects which were reopened on a certain date but only those that were set as terminated before they were reopened. This because it is also possible to reopen them without they were set as terminated. I know it is not logic but it is like that.

Edit: it is possible a project gets reopened twice during a project.
If the two subsequent records are reopened you should only count the reopen of the first date/timestamp. If the reopened are not subsequent, they should be counted on both timestamps.

2

Why not just do a WHERE ? eg.

WHERE status = 'reopened'

3

I understand it as your table only contain status changes to the project, as an audit table.

In that case, for what you want the easiest is just to join the same table with the requirement, something like this:

SELECT
	t.*
FROM
	table as t
	INNER JOIN table as t2
		ON (t.Projectid=t2.Projectid
				AND t2.status='terminated'
				AND t.timestamp>t2.timestamp)
WHERE
	t.status='reopened'
1 Like
4

Not possible. Because it is also possible a project get a status reopened without it was being terminated

5

So can the project have some other states than terminated before it gets reopened? If so maybe you could edit a bit the query by @TheRedDevil.

This part:
AND t2.status='terminated'
to
AND t2.status in ('terminated', 'other_state_than_terminated', 'even_more_states')

?

6

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.