I am trying to write query which will calculate time difference between rows in but I fail horribly.
Problem is that events are not always one after another and if this is the case it should return NULL value or skip it completely.
Example table
| Time | ComputerName | RenderedDescription | EventDisplayNumber |
|---|---|---|---|
| 2022-05-19 14:12:58.400 | COMP16 | The Event log service was started. | 6005 |
| 2022-05-19 13:52:46.360 | COMP16 | The Event log service was stopped. | 6006 |
| 2022-05-19 13:15:47.417 | COMP16 | The Event log service was started. | 6005 |
| 2022-05-08 08:26:11.777 | COMP16 | The Event log service was started. | 6005 |
| 2022-05-08 07:30:11.030 | COMP16 | The Event log service was stopped. | 6006 |
| 2022-05-01 08:22:37.553 | COMP16 | The Event log service was started. | 6005 |
| 2022-05-01 07:30:08.057 | COMP16 | The Event log service was stopped. | 6006 |
| 2022-04-24 08:20:55.190 | COMP16 | The Event log service was started. | 6005 |
| 2022-04-24 07:30:07.143 | COMP16 | The Event log service was stopped. | 6006 |
And result which I am trying to get
| ComputerName | Event Stopped | Event Started | DifferenceMinutes |
|---|---|---|---|
| COMP16 | 2022-04-24 07:30:07.143 | 2022-04-24 08:20:55.190 | 50 |
| COMP16 | 2022-05-01 07:30:08.057 | 2022-05-01 08:22:37.553 | 52 |
| COMP16 | 2022-05-08 07:30:11.030 | 2022-05-08 08:26:11.777 | 56 |
| COMP16 | NULL | 2022-05-19 13:15:47.417 | NULL |
| COMP16 | 2022-05-19 13:52:46.360 | 2022-05-19 14:12:58.400 | 20 |
CodePudding user response：
This will work on your sample data:
with Data as (
select *,
case when RenderedDescription like '%started%' then 'S'
when RenderedDescription like '%stopped%' then 'E' end as Code,
count(case when RenderedDescription like '%started%' then 1 end)
over (partition by ComputerName order by "Time" desc) as Grp
from EventLog
)
select
ComputerName,
max(case when Code = 'E' then "Time" end) as EventStopped,
min(case when Code = 'S' then "Time" end) as EventStarted,
datediff(second,
max(case when Code = 'E' then "Time" end),
min(case when Code = 'S' then "Time" end)
) / 60 as DifferenceMinutes
from Data
group by ComputerName, Grp
order by ComputerName, EventStarted;
If you can both have consecutive starts and stops then try this instead:
with Data as (
select *,
case when RenderedDescription like '%started%' then 'S'
when RenderedDescription like '%stopped%' then 'E' end as Code,
lag(
case when RenderedDescription like '%started%' then 'S'
when RenderedDescription like '%stopped%' then 'E' end
) over (partition by ComputerName order by "Time") as LastCode
from EventLog
), Grouped as (
select *,
count(case when LastCode = 'E' and Code = 'S' then null else 1 end)
over (partition by ComputerName order by "Time") as Grp
from Data
)
select
ComputerName,
max(case when Code = 'E' then "Time" end) as EventStopped,
min(case when Code = 'S' then "Time" end) as EventStarted,
datediff(second,
max(case when Code = 'E' then "Time" end),
min(case when Code = 'S' then "Time" end)
) / 60 as DifferenceMinutes
from Grouped
group by ComputerName, Grp
order by ComputerName, EventStarted;
https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=d7bd556c49cbca84d025bcd1eedc9771