Reading a log file and Matching up the Start and End rows

Question

I have a process that writes to a cs_process_log. It inserts a START row at the beginning of certain processes and then an END row at the end of the process. I want to match up the START with END rows to get a minutes DATEDIFF. Under certain conditions a process might insert multiple MESSAGEs between the START and END rows. I want to show these messages also and want to catch any START rows that do not have a matching END row, which would mean a failed condition.

I have played with it for awhile now. but have not been able to come up with a working solution. Any help would be appreciated.

This is the output that i am hoping to get. (it is in desc order)

drop table cs_process_log  
create table cs_process_log (recid int identity, process_name varchar(max), category varchar(max), run_datetime datetime, notes varchar(max))  
  
insert into cs_process_log  
select process_name, category, run_datetime, notes from   
(values   
('SECURITY', 'START', '2021-08-01 14:35:39.067', ''),  
('SECURITY', 'END',   '2021-08-01 14:50:39.067', ''),  
('DASHBOARD', 'START', '2021-08-01 15:01:39.067', ''),  
('DASHBOARD', 'END',   '2021-08-01 15:08:39.067', ''),  
  
('SECURITY', 'START', '2021-08-01 16:35:39.067', ''),  
('SECURITY', 'END',   '2021-08-01 16:50:39.067', ''),  
('DASHBOARD', 'START', '2021-08-01 17:01:39.067', ''),  
('DASHBOARD', 'END',   '2021-08-01 17:08:39.067', ''),  
  
('SECURITY', 'START', '2021-08-02 14:35:39.067', ''),  
('SECURITY', 'END',   '2021-08-02 14:50:39.067', ''),  
('DASHBOARD', 'START', '2021-08-02 15:01:39.067', ''),  
('DASHBOARD', 'MIDDLE STEP2', '2021-08-02 15:03:39.067', ''),  
  
('SECURITY', 'START', '2021-08-03 14:35:39.067', ''),  
('SECURITY', 'END',   '2021-08-03 14:50:39.067', ''),  
('DASHBOARD', 'START', '2021-08-03 15:01:39.067', ''),  
('DASHBOARD', 'MIDDLE STEP3', '2021-08-03 15:03:39.067', ''),  
('DASHBOARD', 'END',   '2021-08-03 15:08:39.067', ''),  
  
('SECURITY', 'START', '2021-07-25 14:35:39.067', ''),  
('SECURITY', 'END',   '2021-07-25 14:50:39.067', ''),  
('DASHBOARD', 'START', '2021-07-25 15:01:39.067', ''),  
('DASHBOARD', 'END',   '2021-07-25 15:08:39.067', ''))  
 a (process_name, category, run_datetime, notes)  

Answer 1

Hi @brenda grossnickle ,

Welcome to Microsoft Q&A!

Please refer below query and check whether it is working.

;with cte as (  
SELECT *,COUNT(CASE category WHEN 'Start' THEN 1 END) OVER (PARTITION BY process_name ORDER BY recid) AS Grp  
FROM cs_process_log  
WHERE category IN ('Start','End'))  
,cte1 as (  
SELECT process_name,run_datetime, category,  
       MAX(CASE category WHEN 'Start' THEN run_datetime END) over (partition by process_name,Grp) AS run_datetime_start,  
       MAX(CASE category WHEN 'End' THEN run_datetime END) over (partition by process_name,Grp) AS run_datetime_end  
FROM cte  
union   
SELECT process_name,run_datetime,category,run_datetime,NULL  
from cs_process_log  
where category not IN ('Start','End'))  
select distinct DATEDIFF(MINUTE,run_datetime_start,run_datetime_end) MINUTES_RUN,  
process_name,category,run_datetime_start,run_datetime_end from cte1  
where category<>'end'  
order by run_datetime_start desc  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

As we see, common table expressions (CTE in SQL Server) can provide us with a convenient way to query data similar to using tools like temp tables and subqueries. We have quick troubleshooting options with these where we can run select statements inside the wrapping of the CTE and we can create multiple statements to join together. Are these faster? In some cases, they may be, or they may be faster to troubleshoot – which could be important. Still, we should always test our queries to ensure that the way we’re running our query is the most optimal route.

Answer 3

That first line was the missing piece. That COUNT with the CASE statement was great.

SELECT *,COUNT(CASE category WHEN 'Start' THEN 1 END) OVER (PARTITION BY process_name ORDER BY recid) AS Grp