[SQL] 'Jagged' self join?

SiD the Turtle · 24 Dec 2008 at 13:42

Hi guys, I have a bit of a puzzle I'm getting nowhere with. Consider the following table:

Code:

id        | value      | auditkey
---------------------------------
1         | 10         | 5
1         | 8          | 3
1         | 4          | 2
2         | 20         | 5
2         | 4          | 4

What I need to do is turn this data into the form that for each id, I have the difference in value between one row and the next smallest audit key, see below with additional explanation:

Code:

id        | diff
----------------
1         | 2         (Auditkey 5->3: 10 - 8 = 2
1         | 4         (Auditkey 3->2: 8 - 4 = 4
2         | 16        (Auditkey 5->4: 20 - 4 = 16

So in theory what I want to do is select the entire table, order it by id and then audit key (descending) and then join it to the same table shifted down one (ish, the last row for each id would end up being ignored).

Any ideas? This is in SQL Server 2005 if it helps, so I don't have access to unique functions like MySQL's LIMIT.

PsychoDuck · 24 Dec 2008 at 15:17

How many different audit keys are there?

SiD the Turtle · 24 Dec 2008 at 15:23

n

Basically every day more data is loaded, therefore there will be a new auditkey (an autoincrement) every day.

PsychoDuck · 24 Dec 2008 at 16:00

Try this, it wont be the most efficient as I have had to use a cursor:-

Code:

--select * from tblTest order by id, auditkey desc

-- put entries with more than one id entry into a temp table
select id, value, auditkey
INTO #tmpTest
FROM tblTest
where id in (select id from tblTest group by id having count(id) > 1)
order by id, auditkey desc

--select * from #tmpTest

-- create temp table for results
create table #tmpResults
(
id int,
diff int
)

--select * from #tmpTest

DECLARE @id int, @value int, @auditkey int
DECLARE @nextId int, @nextValue int, @nextAuditKey int

DECLARE rowCursor CURSOR FOR
SELECT id, value, auditkey
FROM #tmpTest
order by id, auditkey desc
OPEN rowCursor

FETCH NEXT FROM rowCursor INTO @id, @value, @auditkey

WHILE @@FETCH_STATUS = 0

	BEGIN

	select top 1 @nextId = id, @nextValue = value, @nextAuditKey = auditKey from #tmpTest where id = @id and auditkey < @auditKey

	if @id = @nextId and @auditKey != @nextAuditKey
	begin

	insert into #tmpResults select @id, @value - @nextValue

	--PRINT 'Current Values-' + convert(varchar, @id) + ' ' + convert(varchar, @value) + ' ' + convert(varchar, @auditkey) + ' Next Values-' + convert(varchar, isnull(@nextId,'')) + ' ' + convert(varchar, isnull(@nextValue,'')) + ' ' + convert(varchar, isnull(@nextAuditKey,''))	
	
	end

	FETCH NEXT FROM rowCursor INTO @id, @value, @auditkey

	END

CLOSE rowCursor
DEALLOCATE rowCursor
DROP TABLE #tmpTest 

select * from #tmpResults

drop table #tmpResults

It assumes that there will only be one entry for each audit key / id pair.

SiD the Turtle · 26 Dec 2008 at 13:50

Thanks matey. My implementation of your idea (the actual data is a bit more complex) was a bit expensive but we're still only talking seconds here as I don't have reams of data.

In the end I created quite a nasty intermediate table that maps the id/auditkey pair to the id/auditkey I want to find the difference of, and then joined this to the actual table. Messy, only slightly faster than a cursor (lots of joins going on) but will be easier to read when I come to look at the code in a few months time