Working with Row Calculations

[ejiro]

Hi,

I have a table “TblBlocks”, which consists of columns “blockID”, “blockHeight” and “ProcessStep”.

BlockID BlockHeight ProcessStep
B52 2.85 1
A24 3.42 1
A41 3.23 1
A24 3.22 2
B52 2.52 2
A24 3.01 3
B52 2.08 3
A41 2.95 2
B52 1.86 4
A24 2.65 4
A41 2.51 3
B52 1.62 5
A41 2.14 4
A24 2.53 5
B52 1.32 6

I need a query or solution module to show me the relative difference in the height of blocks in the consecutive process steps of all blocks in the table.

The result should look like this:

BlockID HeightDifference ProcessStep
A24 0.2 2
A24 0.21 3
A24 0.36 4
A24 0.12 5
A41 0.28 2
A41 0.44 3
A41 0.37 4
B52 0.33 2
B52 0.44 3
B52 0.22 4
B52 0.24 5
B52 0.3 6

I have tried using crosstab query as an intermediate step, but that makes the solution complicated and cumbersome. Is another way to do this?
If you could provide an example that would help. Thanks...

 

[cheerio]

I think you need to join the table to itself. Something like:

select A.BlockID,
       A.BlockHeight - B.BlockHeight as HeightDiff,
       A.ProcessStep
from   TblBlocks A
join   TblBlocks B
on     A.BlockID=B.BlockID
and    A.ProcessStep = B.ProcessStep+1

This assumes ProcessSteps have no gaps otherwise it becomes trickier.

 

[ejiro]

Nice idea, but process may have jumps.

 

[PHV]

And what about this ?
SELECT A.BlockID, A.BlockHeight - B.BlockHeight As HeightDifference, A.ProcessStep
FROM TblBlocks AS A INNER JOIN TblBlocks AS B ON A.BlockID=B.BlockID
WHERE B.ProcessStep = (SELECT Min(C.ProcessStep) FROM TblBlocks C WHERE C.BlockID=A.BlockID AND C.ProcessStep>A.ProcessStep)
ORDER BY 1, 3;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PHV]

Sorry for the typo, replace this:
SELECT A.BlockID, A.BlockHeight - B.BlockHeight As HeightDifference, A.ProcessStep
By this:
SELECT A.BlockID, A.BlockHeight - B.BlockHeight As HeightDifference, B.ProcessStep

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ejiro]

PHV,

Your solution worked. Excellent work!