mohan111 mohan111 - 3 months ago 9
SQL Question

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

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

Answer

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';