Sql query to determine the status?

Question

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

Timestamp (datetime)
Message (varchar(20))

Once a day, a specific process inserts the current time and the message "Start" when it starts. When it is completed, it inserts the current time and the message “Finish”.

What is a good query or a set of instructions that, given a specific date, are returned:

• 0 if the process did not start
• 1, if the process began, but did not end
• 2, if the process started and ended

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

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

Answer 1

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

, , , , , ..

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

You are missing a column that uniquely identifies the process. Let's add an int column called ProcessID. You will also need another table to identify the processes. If you rely on your original table, you will never know about processes that never started, because there would be no row for this 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

Answer 4

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

Alternatively, you can use self-connecting with max to display the most recent message on a particular day:

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