1

I have a series of rows representing either start (S) or and (E) is a thing

+----+-------+-------+
| Id | Event | Thing |
+----+-------+-------+
|  1 | S     | A     |
|  2 | E     | A     |
|  3 | S     | B     |
|  4 | E     | B     |
|  5 | S     | C     |
|  6 | S     | D     |
|  7 | S     | E     |
+----+-------+-------+

I'm trying to get the rows that have no end event.

SELECT [a].[Id] AS [ID a],
       [a].[Event] AS [Event a],
       [b].[Id] AS [ID b],
       [b].[Event] AS [Event b]
  FROM [dbo].[TimeSeries] AS [a]
  LEFT OUTER JOIN [dbo].[TimeSeries] AS [b]
    ON [b].[Thing] = [a].[Thing]
   WHERE [a].[Event] = 'S'
   AND [b].[Event] = 'E'

This returns

+------+---------+------+---------+
| ID a | Event a | ID b | Event b |
+------+---------+------+---------+
|    1 | S       |    2 | E       |
|    3 | S       |    4 | E       |
+------+---------+------+---------+

This is where I'm getting stuck.

I want all the IDs that are neither in the ID a column nor the ID b column. Then I'm guessing LEFT JOIN to find all the rows that have no end event.

NB: The real table is an SSIS log with many millions of rows.

  • Please add your sql-server version – McNets Jun 18 '17 at 14:41
  • The WHERE-condition changes the Outer Join to Inner. When you move AND [b].[Event] = 'E' from WHERE to ON you can simply add AND [b].[Event] IS NULL to get your result. But Martin Smith's NOT EXISTS is probably performing better. – dnoeth Jun 18 '17 at 16:31
5

Here's a few ways.

  1. Use NOT EXISTS for an anti semi join
SELECT Id,
       Event,
       Thing
FROM   dbo.TimeSeries t1
WHERE  NOT EXISTS(SELECT *
                  FROM   dbo.TimeSeries t2
                  WHERE  t1.Thing = t2.Thing
                         AND t2.Event = 'E');
  1. Partition by Thing and find the minimum (alphabetically first) event in each partition. If it is S rather than E this Thing has no end event.
WITH T
     AS (SELECT *,
                MIN(Event) OVER (PARTITION BY Thing) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Evt = 'S';
  1. Partition by Thing and use LAG to look at the preceding row to the current row when ordering by event alphabetically. If the current row is S and there is no preceding row Evt will be NULL. Just preserve any such rows. This is 2012+ syntax.
WITH T
     AS (SELECT *,
                LAG(Event) OVER (PARTITION BY Thing ORDER BY Event) AS Evt
         FROM   dbo.TimeSeries
         WHERE  Event IN ( 'E', 'S' ))
SELECT Id,
       Event,
       Thing
FROM   T
WHERE  Event = 'S' AND Evt IS NULL;
  • I think the original poster would be helped a lot if you would provide some extra explanation about why your queries give the intended result... specially if s/he doesn't know how to deal with CTE (WITH) and WINDOWS (OVER). – joanolo Jun 18 '17 at 16:24
  • Cheers, that seems to work perfectly. What's LAG? – BanksySan Jun 18 '17 at 16:57
  • Just found it, extra kudos for using the latest syntax! – BanksySan Jun 18 '17 at 17:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.