我在MSSQL数据库中有一个看起来像这样的表：

Timestamp (datetime)
Message (varchar(20))

每天一次，特定进程将在启动时插入当前时间和消息"已启动"。完成后，它会插入当前时间和消息" Finished"。

在给定特定日期的情况下，返回以下内容的是一种好的查询或者一组语句：

• 如果该进程从未开始，则为0
• 如果该过程已开始但尚未完成，则为1
• 2该过程是否已开始和结束

表格中还有其他消息，但是"开始"和"完成"对于此过程是唯一的。

编辑：对于奖励因果报应，如果数据无效，则引发错误，例如，有两个"已开始"消息，或者有一个"已完成"而没有"已开始"的消息。

解决方案

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

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

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

或者，我们可以使用带有最大值的自连接来显示特定日期的最新消息：

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

我们缺少唯一标识该过程的列。让我们添加一个称为ProcessID的int列。我们还需要另一个表来标识进程。如果我们依赖原始表，那么我们将永远不会知道从未启动的进程，因为该进程不会有任何行。

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