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

Answer 2

DECLARE @TargetDate datetime
SET @TargetDate = '2008-01-01'

DECLARE @Messages varchar(max)

SET @Messages = ''

SELECT @Messages = @Messages + '|' + Message
FROM process_monitor
WHERE @TargetDate <= Timestamp and Timestamp < DateAdd(dd, 1, @TargetDate)
   and Message in ('Finished', 'Started')
ORDER BY Timestamp desc

SELECT CASE
  WHEN @Messages = '|Finished|Started' THEN 2
  WHEN @Messages = '|Started' THEN 1
  WHEN @Messages = '' THEN 0
  ELSE -1
END

Answer 3

select count(*) from process_monitor 
where timestamp > yesterday and timestamp < tomorrow.

Alternately, you could use a self join with a max to show the newest message for a particular day:

select * from process_monitor where 
timestamp=(select max(timestamp) where timestamp<next_day);

Answer 4

You are missing a column that uniquely identifies the process. Lets add a int column called ProcessID. You would also need another table to identify processes. If you were relying on your original table, you'd never know about processes that never started because there wouldn't be any row for that process.

select
    ProcessID,
    ProcessName,

    CASE
    WHEN 
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'STARTED') = 1 

        And
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'FINISHED') = 0
     THEN 1

     WHEN
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'STARTED') = 1 
       And
       (Select 
           COUNT(*) 
        from 
           ProcessActivity 
        where 
           ProcessActivity.processid = Processes.processid 
           and Message = 'FINISHED') = 1 
THEN 2
     ELSE 0

END as Status

From
    Processes