January 23, 2006 at 7:38 am
Hi all, I have a requirement where I need to make a calculation that makes use of two value where each value is in a different row.
For example, if I have the following three columns I want the two rows that have the same ID and for the row that has the value 912 in the Ref column minus the number in the Value column from the number in the same column (Value) that has the same ID but has 911 in the Ref column.
Code:
ID Ref Value
2674198 911 5.01374
2674198 912 3.78280
2674199 911 3.61368
2674199 912 8.85212
Does anyone know how I might get started with this?
Thanks
Tryst
January 23, 2006 at 10:01 am
There are couple of different ways...if you know the value in the REF column i.e. if those are static values that you will be aware of, you can do this:
SET NOCOUNT ON
DECLARE @test-2 TABLE (ID INT, REF INT, VALUE NUMERIC(8,5))
INSERT INTO @test-2 VALUES (2674198, 911, 5.01374)
INSERT INTO @test-2 VALUES (2674198, 912, 3.78280)
INSERT INTO @test-2 VALUES (2674199, 911, 3.61368)
INSERT INTO @test-2 VALUES (2674199, 912, 8.85212)
SELECT * FROM @test-2
SELECT ID,
SUM(
CASE WHEN REF = 911 THEN VALUE ELSE 0 END
-
CASE WHEN REF = 912 THEN VALUE ELSE 0 END
) AS VALUE
FROM @test-2
GROUP BY ID
--output
ID VALUE
----------- ----------------------------------------
2674198 1.23094
2674199 -5.23844
January 23, 2006 at 10:43 am
Hi rsharma, thanks for the reply.
I actually got to another solution with the help from someone on another forum.
This is the solution...
select t911.id
, t912.value - t911.value as diff
from yourtable as t911
inner
join yourtable as t912
on t911.id = t912.id
where t911.ref = 911
and t912.ref = 912
Thanks
Tryst
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply