1

I've a SQL table of start times and finish times for several different cycles running through a system. The table below is a simplified sample of the data I'm dealing with.

Batch# Event# Event Timestamp 1 1 Start 2015-07-01 12:31:31.000 1 2 Start 2015-07-01 12:31:31.000 1 3 Start 2015-07-01 12:31:31.000 1 4 End 2015-07-01 12:33:32.000 1 5 End 2015-07-01 12:33:32.000 1 6 End 2015-07-01 12:33:32.000 2 1 Start 2015-07-01 12:35:32.000 2 2 End 2015-07-01 12:36:32.000

I want to produce a table which looks something like this.

Batch# Event# Event Timestamp Event# Event Timestamp 1 1 Start 2015-07-01 12:31:31.000 4 End 2015-07-01 12:33:32.000 1 2 Start 2015-07-01 12:31:31.000 5 End 2015-07-01 12:33:32.000 1 3 Start 2015-07-01 12:31:31.000 6 End 2015-07-01 12:33:32.000 2 1 Start 2015-07-01 12:35:32.000 2 End 2015-07-01 12:36:32.000

I've tried using a left outer join but I'm getting extra rows.

SELECT * FROM Table1 as T1 LEFT OUTER JOIN Table1 as T2 ON T1.Batch_no = T2.Batch_no WHERE T1.Event = 'Start' AND T2.Event = 'End' Batch# Event# Event Timestamp Event# Event Timestamp 1 1 Start 2015-07-01 12:31:31.000 1 4 End 2015-07-01 12:33:32.000 1 1 Start 2015-07-01 12:31:31.000 1 5 End 2015-07-01 12:33:32.000 1 1 Start 2015-07-01 12:31:31.000 1 6 End 2015-07-01 12:33:32.000 1 2 Start 2015-07-01 12:31:31.000 1 4 End 2015-07-01 12:33:32.000 1 2 Start 2015-07-01 12:31:31.000 1 5 End 2015-07-01 12:33:32.000 1 2 Start 2015-07-01 12:31:31.000 1 6 End 2015-07-01 12:33:32.000 1 3 Start 2015-07-01 12:31:31.000 1 4 End 2015-07-01 12:33:32.000 1 3 Start 2015-07-01 12:31:31.000 1 5 End 2015-07-01 12:33:32.000 1 3 Start 2015-07-01 12:31:31.000 1 6 End 2015-07-01 12:33:32.000 2 1 Start 2015-07-01 12:35:32.000 2 2 End 2015-07-01 12:36:32.000
Improve this question
4
  • 1
    Can you tell or explain how should we connect start and end? As I see you don't use Event# columns Commented Sep 7, 2015 at 13:33
  • Are Batch# and Event# unique? Commented Sep 7, 2015 at 13:33
  • 1
    I think your data is oversimplified. You've left out any way of telling which end corresponds to which start. Commented Sep 7, 2015 at 13:36
  • 1
    The first start should be connected to the first end. The second start to the second end, etc. Batch# and Event# are not unique individually but the combination of both is unique. I possibly have oversimplified because the database is quite complicated and I've only been using SQL and databases for a week, so I apologise if that is an issue. Commented Sep 7, 2015 at 13:39

2 Answers 2

Reset to default
1

One way to do this:

You can use row_number() to number the rows in each partition, once for start and once for end and then do a join.

with cte_start as ( select *, r_start = row_number() over (partition by [batch#] order by [event#]) from t where [event] = 'Start'), cte_end as ( select *, r_end = row_number() over (partition by [batch#] order by [event#]) from t where [event] = 'End') select s.[Batch#], s.[Event#], s.[Event], s.[Timestamp], e.[Event#], e.[Event], e.[Timestamp] from cte_start s join cte_end e on s.Batch# = e.Batch# and s.r_start = e.r_end

Sample SQl Fiddle

Sample result:

| Batch# | Event# | Event | Timestamp | Event# | Event | Timestamp | |--------|--------|-------|------------------------|--------|-------|------------------------| | 1 | 1 | Start | July, 01 2015 12:31:31 | 4 | End | July, 01 2015 12:33:32 | | 1 | 2 | Start | July, 01 2015 12:31:31 | 5 | End | July, 01 2015 12:33:32 | | 1 | 3 | Start | July, 01 2015 12:31:31 | 6 | End | July, 01 2015 12:33:32 | | 2 | 1 | Start | July, 01 2015 12:35:32 | 2 | End | July, 01 2015 12:36:32 |
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

As best I can tell this works perfectly. Thank you very much.
0

This should work just fine. Let me know if it needs any tweaks.

SQL Server 2008 and above

WITH CTE_YourTable --My version of your table AS ( SELECT row_num = ROW_NUMBER() OVER (PARTITION BY Batch#,[Event] ORDER BY Event#), --This numbers the events for each batch and type. * FROM ( VALUES (1, 1, 'Start', '2015-07-01 12:31:31.000'), (1, 2, 'Start', '2015-07-01 12:31:31.000'), (1, 3, 'Start', '2015-07-01 12:31:31.000'), (1, 4, 'End', '2015-07-01 12:33:32.000'), (1, 5, 'End', '2015-07-01 12:33:32.000'), (1, 6, 'End', '2015-07-01 12:33:32.000'), (2, 1, 'Start', '2015-07-01 12:35:32.000'), (2, 2, 'End', '2015-07-01 12:36:32.000') ) AS A(Batch#,Event#,[Event],[Timestamp]) ) SELECT A.Batch#, A.Event#, A.[Event], A.[Timestamp], B.Event#, B.[Event], B.[Timestamp] FROM CTE_YourTable AS A LEFT JOIN CTE_YourTable AS B ON A.Batch# = B.Batch# --Only join matching batches AND A.row_num = B.row_num --Match the rows so the first start goes with the first end, second start to second end etc... AND B.[Event] = 'End' --Only end events on the right table aliased "B" WHERE A.[Event] = 'Start' --Only start events on the left table aliased "A" ORDER BY A.Batch#,A.Event#
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.