<p>I have a table in a MSSQL database that looks like this:</p>

<pre><code>Timestamp (datetime)
Message (varchar(20))
</code></pre>

<p>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'.</p>

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

<ul>
<li>0 if the process never started</li>
<li>1 if the process started but did not finish</li>
<li>2 if the process started and finished</li>
</ul>

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

<p>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'.</p>

```
```
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
```
```