Matching transactions in a single table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Matching transactions in a single table

Author

Topic

wrwessel
Starting Member

3 Posts

Posted - 2012-02-23 : 20:29:50

I have a table that has events in it and I need to match the start and end events (1 and 2) together as a single row where the tank also matches. e.g.

Source table is:

Time Tank Volume Event
---- ---- ------ -----
a    51   A      1
b    51   B      2
c    52   C      1
d    51   D      1
e    52   E      2
f    51   F      2
g    52   G      2

Expected result would be:

Start End  Tank Start End
Time  Time      Vol   Vol
----- ---- ---- ----- ----
a     b    51   A     B
c     e    52   C     E
d     f    51   D     F
      g    52   0     G

Note in row d/e that the start event of a tank may occur before the end of another tank (i.e. end events do not always follow 1 row after the start).

Note in row g there could be a missing start (or end) event that needs to show a missing start or end time. Although this event should not be missing it becomes important to highlight the mising information.

Actual source table is:

CREATE TABLE [dbo].[BulkTank](
	[Time] [datetime2](7) NOT NULL,
	[Tank] [smallint] NOT NULL,
	[Volume] [real] NOT NULL,
	[Event] [smallint] NOT NULL,
 CONSTRAINT [PK_BulkTank] PRIMARY KEY CLUSTERED 
(
	[Time] ASC,
	[Tank] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Not sure how to get the DML of the data in SQL 2008:

Time	Tank	Volume	Event
2011-11-27 17:13:55.0000000	52	1811221	2
2011-11-27 20:27:51.0000000	53	1760992	1
2011-11-27 20:33:25.0000000	51	1734139	2
2011-11-28 00:51:15.0000000	53	1759984	2
2011-11-28 01:06:09.0000000	52	1802831	1
2011-11-28 05:22:19.0000000	51	1791526	1
2011-11-28 05:28:45.0000000	52	1802604	2
2011-11-28 13:26:49.0000000	51	1791175	2
2011-11-28 14:34:47.0000000	53	1813624	1
2011-11-28 19:31:56.0000000	52	1714030	1
2011-11-28 23:05:32.0000000	53	1812823	2
2011-11-29 03:21:00.0000000	52	1713348	2
2011-11-29 04:49:07.0000000	51	1790470	1
2011-11-29 09:56:16.0000000	53	1812152	1
2011-11-29 10:29:11.0000000	51	1789892	2
2011-11-29 17:33:11.0000000	52	1811017	1
2011-11-29 19:18:03.0000000	53	1812249	2
2011-11-30 00:09:30.0000000	51	1805344	1
2011-11-30 00:09:51.0000000	52	1810730	2
2011-11-30 04:49:36.0000000	53	1813911	1
2011-11-30 05:16:51.0000000	51	1804602	2
2011-11-30 10:33:38.0000000	52	1732870	1
2011-11-30 10:55:37.0000000	53	1814008	2
2011-11-30 16:03:21.0000000	51	1783570	1
2011-11-30 20:13:48.0000000	52	1732830	2
2011-11-30 23:57:53.0000000	51	1783218	2
2011-12-01 00:10:20.0000000	53	1785253	1
2011-12-01 03:36:34.0000000	53	1785025	2
2011-12-01 08:23:25.0000000	51	1783013	1
2011-12-01 08:23:29.0000000	52	1790405	2
2011-12-01 15:42:18.0000000	51	1782371	2
2011-12-01 16:08:05.0000000	53	1813146	1
2011-12-01 20:32:50.0000000	53	1812069	2
2011-12-01 20:33:07.0000000	52	1733966	1
2011-12-02 01:10:29.0000000	52	1733512	2
2011-12-02 01:11:10.0000000	51	1764151	1
2011-12-02 07:01:15.0000000	51	1763761	2
2011-12-02 07:06:01.0000000	53	1813408	1
2011-12-02 12:35:39.0000000	53	1813565	2
2011-12-02 14:36:01.0000000	52	1811205	1
2011-12-02 20:10:17.0000000	51	1772673	1
2011-12-02 20:21:05.0000000	52	1811254	2
2011-12-03 02:53:47.0000000	51	1771210	2
2011-12-03 10:04:24.0000000	53	1808876	2
2011-12-03 10:10:23.0000000	52	1811038	1
2011-12-03 17:19:40.0000000	51	1772572	1
2011-12-03 18:46:20.0000000	52	1811087	2
2011-12-03 22:12:20.0000000	51	1771992	2
2011-12-03 22:12:47.0000000	53	1790809	1
2011-12-04 01:25:07.0000000	52	1761175	1
2011-12-04 01:26:53.0000000	53	1790852	2
2011-12-04 07:20:13.0000000	52	1761210	2
2011-12-04 07:41:54.0000000	51	1803888	1
2011-12-04 13:59:11.0000000	53	1811650	1

So far I have the following query which works but does not include the transactions that have a missing start or end event.

WITH tankSettlingList AS (
	SELECT RN = Row_Number() OVER(order by [Tank], [Time]), [Time], [Tank], [Volume], [Event]
	FROM [BulkTank]
	WHERE ([Event] = 1 or [Event] = 2)
)
SELECT
		[Current Row].Time as StartTime,
		[Next Row].Time as EndTime,
		[Current Row].Tank as TankNumber,
		[Current Row].Volume as StartVolume,
		[Next Row].Volume as EndVolume,
FROM tankSettlingList [Current Row]
LEFT JOIN tankSettlingList [Next Row] ON
	[Next Row].RN = [Current Row].RN + 1
WHERE ([Current Row].Event = 1 and [Next Row].Event = 2) and ([Current Row].Tank = [Next Row].Tank)
ORDER BY StartTime

If there is a better way to do this (stored procedure or program via C#.NET) then let me know. At this point I am just trying to write it as a DataSet in SQL Server Report Builder 2008 but I could put a C# interface around the report. Hope that this has the information you need to help me and thanks for your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 21:10:18

isnt this enough?


SELECT t1.Time AS StartTime, t.Time AS EndTime,t.Tank,
t1.Volume AS StartVol,t.Volume AS EndVol
FROM table t
OUTER APPLY (SELECT TOP 1 *
             FROM table
             WHERE tank = t.tank
             AND Event=1
             AND Time < t.Time
             ORDER BY Time DESC) t1
WHERE Event=2

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wrwessel
Starting Member

3 Posts

Posted - 2012-02-23 : 21:52:45

Wow, that was quick (I'm fairly new to my SQL but I've spent a day on this already).

It's pretty close, but this ends up matching end time g with start time c (start time c has already been matched with end time e). i.e.

Start End  Tank Start End
Time  Time      Vol   Vol
----- ---- ---- ----- ----
a     b    51   A     B
c     e    52   C     E
d     f    51   D     F
c     g    52   C     G

Because the start event should be withing the previous 2 rows, is it possible to do a select top 1 of a select previous 2 with the OUTER APPLY, something like.

SELECT t1.Time AS StartTime, t.Time AS EndTime, t.Tank, 
t1.Volume AS StartVolume, t.Volume AS EndVolume
FROM BulkTank t
OUTER APPLY (
	SELECT TOP 1 * 
	FROM (
		SELECT TOP 2 *
		FROM BulkTank
		WHERE [Time] < t.Time
		ORDER BY [Time] DESC)
	WHERE [Event] = 1
	AND [Tank] = t.tank) t1
WHERE t.[Event]=2

This code gets an error:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'WHERE'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-23 : 22:22:22

[code]
SELECT t1.Time AS StartTime, t.Time AS EndTime,t.Tank,
t1.Volume AS StartVol,t.Volume AS EndVol
FROM table t
OUTER APPLY (SELECT TOP 1 *
FROM table
WHERE tank = t.tank
AND Event=1
AND Time < t.Time
ORDER BY Time DESC) t1
OUTER APPLY (SELECT COUNT(*) AS cnt
FROM table
WHERE tank = t.tank
AND Event=2
AND Time > t1.Time
AND Time < t.Time )t2
WHERE Event=2
AND COALESCE(cnt,0) = 0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wrwessel
Starting Member

3 Posts

Posted - 2012-02-26 : 18:44:31

Thank you, this didn't include the events that did not have a start time but managed to work out how to do this based on what you supplied. I now found it also doesn't include events that have a start but no end time but have had a discussion regarding the clients requirements and decided that unmatched transaction are not required anyway. My first sql would work in this case but I think your statement is more efficient so I'll run with that.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources