Sql query to determine status?

Question

I have a table in a MSSQL database that looks like this:

Timestamp (datetime)
Message (varchar(20))

Once a day, a particular process inserts the current time and the message 'Started' when it starts. When it is finished it inserts the current time and the message 'Finished'.

What is a good query or set of statements that, given a particular date, returns:

• 0 if the process never started
• 1 if the process started but did not finish
• 2 if the process started and finished

There are other messages in the table, but 'Started' and 'Finished' are unique to this one process.

EDIT: For bonus karma, raise an error if the data is invalid, for example there are two 'Started' messages, or there is a 'Finished' without a 'Started'.

Answer 1

Select Count(Message) As Status
From   Process_monitor
Where  TimeStamp >= '20080923'
       And TimeStamp < '20080924'
       And (Message = 'Started' or Message = 'Finished')

You could modify this slightly to detect invalid conditions, like multiple starts, finishes, starts without a finish, etc...

Select  Case When SumStarted = 0 And SumFinished = 0 Then 'Not Started'
             When SumStarted = 1 And SumFinished = 0 Then 'Started'
             When SumStarted = 1 And SumFinished = 1 Then 'Finished'
             When SumStarted > 1 Then 'Multiple Starts' 
             When SumFinished > 1 Then 'Multiple Finish'
             When SumFinished > 0 And SumStarted = 0 Then 'Finish Without Start'
             End As StatusMessage
From    (
          Select Sum(Case When Message = 'Started' Then 1 Else 0 End) As SumStarted,
                 Sum(Case When Message = 'Finished' Then 1 Else 0 End) As SumFinished
          From   Process_monitor
          Where  TimeStamp >= '20080923'
                 And TimeStamp < '20080924'
                 And (Message = 'Started' or Message = 'Finished')
        ) As AliasName