SQL statement to get data without duplicates for a combination of columns

Question

There is an event capture table that we have which contains different type of events (based on EventTypeId) for multiple assets (based on assetId).

There was an application bug in our code which we fixed recently where the end time was not being captured correctly. And endtime is captured when the "Severity" changes for a given event type and asset. But this was being done incorrectly.

I tried the below query to get the start and end time but due to the repeating duplicates, I am unable to get to the correct data.

The SQL which I currently formulated (took cue from : Calculate time between On and Off Status rows SQL Server)

WITH ReportData
AS (SELECT e.Id [EventId]
          ,e.AssetId
          ,e.StartTime
          ,e.Severity
          ,e.EventTypeId
          ,a.Name [AssetName]
          ,ROW_NUMBER() OVER (PARTITION BY e.AssetId ORDER BY e.StartTime) RowNum
          ,ROW_NUMBER() OVER (PARTITION BY e.AssetId ORDER BY e.StartTime)
           - ROW_NUMBER() OVER (PARTITION BY e.AssetId, e.Severity ORDER BY e.StartTime) AS [Group]
    FROM dbo.Event e
        JOIN dbo.Asset a
            ON a.Id = e.AssetId)
SELECT state1.AssetName
      ,state1.AssetId
      ,MIN(state1.StartTime) [START]
      ,MAX(state2.StartTime) [END]
      ,DATEDIFF(SS, MIN(state1.StartTime), MAX(state2.StartTime)) [Duration]
      ,state1.Severity
      ,state1.EventId
FROM ReportData state1
    LEFT JOIN ReportData state2
        ON state1.RowNum = state2.RowNum - 1
WHERE state1.Severity = 'Extreme'
      AND state2.StartTime IS NOT NULL
      AND state1.EventTypeId = 27
GROUP BY state1.AssetName
        ,state1.AssetId
        ,state1.Severity
        ,state1.EventId
        ,state1.[Group]
ORDER BY MIN(state1.StartTime) DESC;

The duplicates look something like this

Can someone give me the way to calculate the start and end times based on status change (event type and asset change for severity), ignoring the duplicates.

Also if you could give me a query to identify the duplicates so that we can delete it, would be awesome!

Marc Guillot · Answer 1 · 2020-04-14T08:03:12.423

You can define a CTE to first remove the duplicates, and then run your Query using that CTE :

with e as (
  select min(Id) as Id,  -- We return the first ID for every duplicate
         AssetId, 
         StartTime, 
         Severity, 
         EventTypeId
  from dbo.Event
  group by AssetId, StartTime, Severity, EventTypeId
),
--- Here comes your Query, using e instead of Event

So, it's going to be :

with e as (
  select min(Id) as Id,  -- We return the first ID for every duplicate
         AssetId, 
         StartTime, 
         Severity, 
         EventTypeId
  from dbo.Event
  group by AssetId, StartTime, Severity, EventTypeId
),
ReportData as (
  SELECT e.Id [EventId]
         ,e.AssetId
         ,e.StartTime
         ,e.Severity
         ,e.EventTypeId
         ,a.Name [AssetName]
         ,ROW_NUMBER() OVER (PARTITION BY e.AssetId ORDER BY e.StartTime) RowNum
         ,ROW_NUMBER() OVER (PARTITION BY e.AssetId ORDER BY e.StartTime)
          - ROW_NUMBER() OVER (PARTITION BY e.AssetId, e.Severity ORDER BY e.StartTime) AS [Group]
  FROM e
       JOIN dbo.Asset a
            ON a.Id = e.AssetId
)
SELECT state1.AssetName
      ,state1.AssetId
      ,MIN(state1.StartTime) [START]
      ,MAX(state2.StartTime) [END]
      ,DATEDIFF(SS, MIN(state1.StartTime), MAX(state2.StartTime)) [Duration]
      ,state1.Severity
      ,state1.EventId
FROM ReportData state1
    LEFT JOIN ReportData state2
         ON state1.RowNum = state2.RowNum - 1
WHERE state1.Severity = 'Extreme'
      AND state2.StartTime IS NOT NULL
      AND state1.EventTypeId = 27
GROUP BY state1.AssetName
        ,state1.AssetId
        ,state1.Severity
        ,state1.EventId
        ,state1.[Group]
ORDER BY MIN(state1.StartTime) DESC;

score 0 · Answer 2 · answered Apr 14 '20 at 08:48

So if you can have same severity for same asset and event multiple times, and not in a row (which actually makes the case difficult), then first we have to know which date field (StartDate or CreatedAt) we can define as a field to order your selection. In my query below I suppose it id CreatedAt and this is how I would prepare a dataset of rows to keep (we don`t just keep some rows and delete some, we also have to update EndTime or StartTime on the row we leave), please also notice the comments:

--First we order the selection by entry time and for each line we want to know which severity will be next and previous 
WITH PrevAndNext AS 
(
    SELECT
        Id,
        AssetId,
        EventTypeId,
        Severity,
        StartTime,
        EndTime,
        CreatedAt,
        LAG(Severity) OVER (PARTITION BY AssetId, EventTypeId ORDER BY CreatedAt ASC) AS PrevSeverity, -- date field for ORDER BY depends on your logic!
        LEAD(Severity) OVER (PARTITION BY AssetId, EventTypeId ORDER BY CreatedAt ASC) AS NextSeverity -- date field for ORDER BY depends on your logic!
    FROM
        Table
)

--From the selection above we define the first and last occurence of each severity event
,FirstAndLast AS
(
    SELECT 
        Id,
        AssetId,
        EventTypeId,
        Severity,
        StartTime,
        EndTime,
        CreatedAt,
        CASE 
            WHEN PrevSeverity IS NULL OR PrevSeverity <> Severity THEN 'FirstOccurence'
            WHEN NextSeverity IS NULL OR NextSeverity <> Severity THEN 'LastOccurence'
            ELSE 'MiddleOccurence'
        END AS Occurence 
    FROM 
        PrevAndNext
)

--Then we suppose we want to keep only the first occurence for each severity event, but we need to pick the EndDate from the last occurence
,MergeStartAndEndTime AS
(
    SELECT 
        Id,
        AssetId,
        EventTypeId,
        Severity,
        StartTime,
        CASE 
            WHEN Occurence = 'FirstOccurence' AND LEAD(Occurence) OVER (PARTITION BY AssetId, EventTypeId ORDER BY CreatedAt ASC) = 'LastOccurence' THEN LEAD(EndTime) OVER (PARTITION BY AssetId, EventTypeId ORDER BY CreatedAt ASC) AS KeepIt -- date field for ORDER BY depends on your logic!
            ELSE EndTime
        END AS EndTime,
        CreatedAt
    FROM 
        FirstAndLast 
    WHERE 
        Occurence IN ('FirstOccurence', 'LastOccurence')
)

--Here is the dataset you want to keep. You can use it to update the EndDate field for Id-s, and then remove all the other Id-s which are not in the dataset. Please check it carefully and first try it on some test dataset with duplicates. Feel free to adjust it for your logic if necessary.
SELECT 
    Id,
    AssetId,
    EventTypeId,
    Severity,
    StartTime,
    EndTime,
    CreatedAt
FROM 
    MergeStartAndEndTime 
WHERE 
    Occurence = 'FirstOccurence';

SQL statement to get data without duplicates for a combination of columns

2 Answers2