How to do the substraction from the same table based on ID

Question

Sample Table Data

Declare @t table (PolicyNumber varchar(10),Val decimal(18,2),Fund varchar(5),Tenure varchar(10))
INSERT INTO @t(PolicyNumber,Val,Fund,Tenure)VALUES 
('ASWPP',10.2,'T001','Secure'),
('ASWPP',5.0,'T002','Secure'),
('ASWPP',5.0,'T003','Secure'),
('XPPW',15.2,'T001','Growth'),
('XPPW',10.2,'T002','Growth'),
('XPPW',10.2,'T003','Growth'),
('XXXX',20.2,'T001','Secure'),
('XXXX',10.2,'T002','Secure'),
('XXXX',10.2,'T003','Secure')

How to do the Subtraction from same table basing on Policy number.

I need to subtract the Fund = 002 from the other funds (T001,T003)

I need to get the output like below :

 PolicyNumber   Val        Fund           Tenure
 ASWPP          5.2        T001            Secure     
 ASWPP          0.0        T003            Secure     
 XPPW           5.2        T001            Growth     
 XPPW           0.2        T003            Growth     
 XXXX           10.2       T001           Secure     
 XXXX           0.0        T003            Secure

Suggest me the best way

where you are not clear ? In T001,T003 need to get subtracted from T002. — mohan111, Aug 22 '16 at 11:07
Should the 5th row (`XXXX, 10.2, T001, Secure`) not have a value of 10 for `Val` (20.2 - 10.2) rather than 10.2? — GarethD, Aug 22 '16 at 11:16

GarethD · Accepted Answer · 2016-08-22T11:13:55.213

You need to join the table to itself, with one instance excluding the T002 records, and one instance only containing them:

SELECT  t.PolicyNumber,
        Val = t.Val - ISNULL(t2.Val, 0),
        t.Fund,
        t.Tenure
FROM    @T AS t
        LEFT JOIN @T AS t2
            ON t2.PolicyNumber = t.PolicyNumber
            AND t2.Fund = 'T002'
WHERE   t.Fund <> 'T002';

If the type of PolicyNumber and Fund is not unique, then you may need a subquery for it to avoid duplication:

SELECT  t.PolicyNumber,
        Val = t.Val - ISNULL(t2.Val, 0),
        t.Fund,
        t.Tenure
FROM    @T AS t
        LEFT JOIN 
        (   SELECT  PolicyNumber, Val = SUM(Val)
            FROM    @T 
            WHERE   Fund = 'T002'
            GROUP BY PolicyNumber
        ) AS t2
            ON t2.PolicyNumber = t.PolicyNumber
WHERE   T.Fund <> 'T002';

I think, expected output is not correct. `XXXX 10.2 T001 Secure ` — neer, Aug 22 '16 at 11:13

score 1 · Answer 2 · answered Aug 22 '16 at 11:07

1

Join that table on itself like this:

SELECT t1.PolicyNumber, t2.val - t1.val AS val, t2.Fund, t2.Tenure
  FROM t t1
  JOIN t t2
    ON t1.PolicyNumber = t2.PolicyNumber AND t2.FUND != 'T002'
 WHERE t1.FUND = 'T002';

answered Aug 22 '16 at 11:07

Razzka

641
5
16

Inner join, right. INNER here is just a syntactic sugar, can be dropped out. Right aligned by pressing spacebars, just a little usefull habit. – Razzka Aug 22 '16 at 11:39
Maybe it's personal opinion, 1) I prefer `INNER JOIN` as explained at http://stackoverflow.com/questions/565620/difference-between-join-and-inner-join; 2) Right aligned code make troubles; You have to be thinking what is the longest keyword in this statement while typing. If you add a HAVING later, will you update all lines above? – qxg Aug 22 '16 at 11:54

score 0 · Answer 3 · answered Aug 22 '16 at 11:23

0

This is one of the way.

Select a.PolicyNumber,
a.Val - t2.Val as NewVal,
a.Fund, a.Tenure
from @t a
join
(
    Select * from @t
    Where Fund = 'T002'
) t2
ON a.PolicyNumber = t2.PolicyNumber
Where a.Fund <> 'T002'

answered Aug 22 '16 at 11:23

p2k

2,126
4
23
39

How to do the substraction from the same table based on ID

3 Answers3