How to check process completion status with SQL query?

Data and Databases
1

I’m working with a database table that tracks different processes. The table has these columns:

LogTime (datetime)
Status (varchar(50))

Every day there’s a specific job that writes two records. First it adds a row with current timestamp and ‘Begin’ when it kicks off. Then it writes another row with timestamp and ‘Complete’ when done.

I need help writing a SQL statement that takes a date as input and gives back these results:

  • Return 0 when the job never ran
  • Return 1 when the job started but didn’t complete
  • Return 2 when the job both started and completed successfully

The table contains other status messages too, but ‘Begin’ and ‘Complete’ are only used by this particular process.

As an extra challenge, it would be great if the query could detect bad data like duplicate ‘Begin’ entries or ‘Complete’ without matching ‘Begin’.

2

interesting problem! what if the ‘complete’ timestamp comes before ‘begin’ though? like if system clocks get messed up? also curious - do you actually need to handle timezones or just working with local time?

3

you can use a case stmnt with conditional counts. try: select case when sum(case when status='Begin' then 1 else 0 end) = 0 then 0 when sum(case when status='Complete' then 1 else 0 end) = 0 then 1 else 2 end just remember to add your date filter in where clause. may need tweaking for dupes.

4

Conditional aggregation works well, but I’d wrap it in a subquery for better duplicate handling. Here’s what I use:

WITH process_counts AS (
    SELECT 
        COUNT(CASE WHEN Status = 'Begin' THEN 1 END) as begin_count,
        COUNT(CASE WHEN Status = 'Complete' THEN 1 END) as complete_count
    FROM your_table 
    WHERE DATE(LogTime) = @input_date
)
SELECT 
    CASE 
        WHEN begin_count = 0 AND complete_count = 0 THEN 0
        WHEN begin_count > 0 AND complete_count = 0 THEN 1
        WHEN begin_count = 1 AND complete_count = 1 THEN 2
        ELSE -1  -- indicates bad data
    END as process_status
FROM process_counts;

The main benefit is returning -1 for weird situations like multiple begins or orphaned completes. I’ve found this explicit bad data handling stops silent failures when process logs get corrupted in production.