I’m trying to retrieve the status for a multi-step process which is stored in a sql server db. Each step is a record in a table.

When a client wants to know the status of that process, I would like to retrieve the statusses for all steps and have my query return a status of

• ‘queued’ when all steps have status ‘queued’,
• ‘in-progress’ when one of the steps has status ‘in-progress’,
• ‘finished’ when all of the steps have status ‘finished’.

What would this query look like?

I’m not that experienced in Sql yet so I haven’t tried anything yet because I wouldn’t know where to start and can’t really find anything like this problem on Google.

Edit based on comment:
Tables look like:

Process
- id uniqueidentifier not null pk
- other stuff

Step
- id uniqueidentifier not null pk
- processId uniqueidentifier not null fk with process.id
- status nvarchar(30) not null

So some sample data for step would be:

which should result in my query returning:

>Solution :

You can check using the following query:

DECLARE @pid int = 7; --whatever the ID of this process is.

SELECT 
   CASE 
   WHEN EXISTS (SELECT 1 FROM ProcessSteps WHERE ProcessID = @pid AND Status = 'in-progress') THEN 'in-progress'
   WHEN NOT EXISTS (SELECT 1 FROM ProcessSteps WHERE ProcessID = @pid AND Status = 'finished') THEN 'queued'
   ELSE 'finished'
   END AS ProcessStatus
FROM ProcessSteps
WHERE ProcessID = @pid

The query first checks if any of the steps are in-progress. If there is any, the status of in-progress is returned. Next is checks if all the steps are finished, and if not returned queued, otherwise it returns finished.