How can I combine 2 existing data with the same Id Li from a single column time data, one as the sta-CodePudding

I have a single-dated column in SQL. And I also have 2 datasets with the same Id. How can I show the older one of these data as the start time and the other as the end time in a single column? First of all, I wrote a query that shows the difference in seconds between two times with the same Id.

The query is like this:

SELECT TOP (1000) 
    [EventAssociationID], [SourceName], [Message], 
    [EventTimeStamp], [Active], [Acked],
    -- TIMESTAMPDIFF(SECOND, [EventTimeStamp]) AS difference,
    DATEDIFF(second, [EventTimeStamp], pTimeStamp) AS TotalTime
FROM 
    (SELECT
         [EventTimeStamp], [EventAssociationID], [SourceName], 
         [Message], [Active], [Acked], 
         LAG([EventTimeStamp]) OVER (PARTITION BY [EventAssociationID] ORDER BY [EventTimeStamp] DESC) pTimeStamp
     FROM 
         [dbo].[Alarms] 
     WHERE
         [SourceName] = 'RSADD_ALM_105' 
         OR [SourceName] = 'RSADD_ALM_106') q

The output is as follows:

EventAssociationID   SourceName      Message      EventTimeStamp       Active Acked TotalTime 
 D1FBB8784      RSADD_ALM_105   TipperLight     '2022-12-07 00:14:34'    0     0     NULL
 D1FBB8784      RSADD_ALM_105   TipperLight     '2022-12-07 00:14:16'    1     0     18
 B6DA7FBD58     RSADD_ALM_106   Curtain         '2022-12-07 11:35:51'    0     0     NULL
 B6DA7FBD58     RSADD_ALM_106   Curtain         '2022-12-07 11:35:01'    1     0     50

The output I am trying to do is I am trying to collect the same "EventAssociationID" data in a single row and the table structure I want is as follows:

EventAssociationID  SourceName     Message    StartTime           EndTime           TotalTime 
  D1FBB8784        RSADD_ALM_105 TipperLight '2022-12-07 00:14:16' '2022-12-07 00:14:34'    18
  B6DA7FBD58       RSADD_ALM_106 Curtain     '2022-12-07 11:35:01' '2022-12-07 11:35:51'    50

I'm trying to create an output like this. There are 2 same data from each EventAssociationID data. I am trying to create the previous time data as the start time and the end time column, and I can calculate the difference in seconds.

How can I edit the SQL query to get the output I want?

CodePudding user response：

Will this work?

select EventAssociationID,SourceName,Message, min(EventTimeStamp) as StartTime, max(EventTimeStamp) as EndTime, sum(TotalTime) as TotalTime
from ...
group by EventAssociationID,SourceName,Message