sql server 2012 merge values from two tables -

i have 2 tables

tbla(sn, id int pk, name varchar(50), amounta decimal(18,2)) 

and

tblb(id int fk, amountb decimal(18,2)) 

here: tbla occures once , tblb may occure multiple time need query display data like:

sn id name amounta   amountb balance 1 1001 abc 5000.00           5000.00 2 1002 xyz 10000.00   1002              4000.00  6000.00 (amounta-amountb) 3 1003 pqr 15000.00    1003              4000.00   1003              3000.00   1003              2000.00  6000.00 (amounta-sum(amountb)) 

please ask if confusion tried using lag , lead function couldnot desire result, please help.

since using sql server 2012, can use partition aggregate function (sum):

select t.sn,        t.id,        t.name,        t.credits amounta,        t.debits amountb,        sum(t.credits - t.debits) on (partition t.id order t.debits, t.credits) balance (     select sn,            id,            name,            amounta credits,            0 debits     tbla     union     select 0 sn,            id,            null name,            0 credits,            amountb debits     tblb ) t order t.id,          t.debits,          t.credits 

explanation:

since records in tables , b each represent single transaction (i.e. credit or debit), using union query bring both sets of data single table works here. after this, compute rolling sum using difference between credit , debit, each record, each id partition group. ordering chosen such credits appear @ top of each partition while debits appear on bottom.