CODEWITHSUNDEEP
sqlsql-server
Matt Howells
Matt Howells

Reputation: 41266

Sql query to determine status?

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:

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

Upvotes: 3

Views: 1679

Answers (4)

George Mastros
George Mastros

Reputation: 24498

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

Upvotes: 2

Amy B
Amy B

Reputation: 110071

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

Upvotes: 0

Aheho
Aheho

Reputation: 12821

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

Upvotes: 0

Grant Johnson
Grant Johnson

Reputation: 1244

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);

Upvotes: -1

Related Questions