CODEWITHSUNDEEP
sql-servergroup-by
Storm2015
Storm2015

Reputation: 11

SQL Grouping columns by row

Struggling with the SQL query to convert the data I have into the required format. I have an event log for machines and would like to associate the start and stop time and event outcome in the same row. I am unable to use LAG due to the version of SQLServer. Any help appreciated.

current dataset:

+----------+----------+------------+------------------------------+---------------------+
| MACHINE  | EVENT_ID | EVENT_CODE |           DATE_TIME          | EVENT_DESCRIPTOR    |
+----------+----------+------------+------------------------------+---------------------+
| 1        | 1        | 1          | 2020-08-06 14:59:26          | SCAN : START : z1 : |
| 1        | 2        | 6          | 2020-08-06 15:00:18          | SCAN : END : z1 :   |
| 1        | 3        | 1          | 2020-08-06 15:00:45          | SCAN : START : z1 : |
| 1        | 4        | 5          | 2020-08-06 15:01:54          | SCAN : ABORT : z1 : |
| 2        | 5        | 1          | 2020-08-06 15:02:15          | SCAN : START : z1 : |
| 2        | 6        | 6          | 2020-08-06 15:05:07          | SCAN : END : z1 :   |
| 1        | 7        | 1          | 2020-08-06 15:05:13          | NEST : START : z1 : |
| 1        | 8        | 6          | 2020-08-06 15:05:22          | NEST : END : z1 :   |
| 1        | 9        | 1          | 2020-08-06 15:07:17          | CUT : START : z1 :  |
| 1        | 10       | 6          | 2020-08-06 15:10:40          | CUT : END : z1 :    |
+----------+----------+------------+------------------------------+---------------------+

The outcome I am trying to achieve:

+----------+------------------------------+------------------------------+----------+
| Machine  | SCAN:START:Z1 _TIME          | SCAN:STOP_OR_ABORT:Z1 _TIME  | OUTCOME  |
+----------+------------------------------+------------------------------+----------+
| 1        | Thu Aug 06 14:59:26 BST 2020 | 2020-08-06 15:00:18          | END      |
| 1        | Thu Aug 06 15:00:45 BST 2020 | 2020-08-06 15:01:54          | ABORT    |
| 1        | Thu Aug 06 15:02:15 BST 2020 | 2020-08-06 15:05:07          | END      |
+----------+------------------------------+------------------------------+----------+

Upvotes: 0

Views: 86

Answers (2)

Marc Guillot
Marc Guillot

Reputation: 6455

You can select the starting events and join them the ending events as subqueries, in the form, for example, of an outer apply.

select L1.Machine, L1.date_time as Start, L2.datetime as Stop_Or_Abort,
       case L2.Event_Id when 5 then 'ABORT' when 6 then 'END' end as Outcome
from MyLogs L1 
     outer apply (select top 1 L2.date_time, L2.Event_Code
                  from MyLogs L2
                  where L2.Machine = L1.Machine and                         
                        L2.Event_ID > L1.Event_ID and
                        L2.Event_Code in (5, 6) 
                  order by L2.Event_ID) as L2
where L1.Event_Descriptor Like 'SCAN%' and
      L1.Event_Code = 1

Upvotes: 1

iceblade
iceblade

Reputation: 641

You can use an Outer Apply to get the next record after the start, filtering only the SCAN events.

Select  Machine = L.MACHINE,
        [SCAN:START:Z1 _TIME] =  L.DATE_TIME,
        [SCAN:STOP_OR_ABORT:Z1 _TIME] = E.DATE_TIME,
        Outcome = Case  when E.EVENT_CODE = 5 then 'ABORT'
                            when E.EVENT_CODE = 6 then 'END'
                End
From Logs L
Outer Apply
(
    Select top 1    L1.DATE_TIME,
                    L1.EVENT_CODE
    From Logs L1
    where   L1.MACHINE = L.MACHINE and
            L1.EVENT_CODE in (5, 6) and
            L1.DATE_TIME > L.DATE_TIME
    order by EVENT_ID
) E
where L.EVENT_CODE = 1
and L.EVENT_DESCRIPTOR like 'SCAN%'

Upvotes: 1

Related Questions