CODEWITHSUNDEEP
sql-serversql-server-2012
Darpan Dahal
Darpan Dahal

Reputation: 45

sql server 2012 merge values from two tables

I have two 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 only once and tblB may occure multiple time I need the query to 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 any confusion I tried using lag and lead function but I couldnot get the desire result, Please help.

Upvotes: 1

Views: 33

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521389

Since you are using SQL Server 2012, you can use a partition with an aggregate function (SUM):

SELECT t.sn,
       t.ID,
       t.name,
       t.credits AS AmountA,
       t.debits AS amountB,
       SUM(t.credits - t.debits) OVER (PARTITION BY t.ID ORDER BY t.debits, t.credits) AS Balance
FROM
(
    SELECT sn,
           ID,
           name,
           AmountA AS credits,
           0 AS debits
    FROM tblA
    UNION ALL
    SELECT 0 AS sn,
           ID,
           NULL AS name,
           0 AS credits,
           amountB AS debits
    FROM tblB
) t
ORDER BY t.ID,
         t.debits,
         t.credits

Explanation:

Since the records in tables A and B each represent a single transaction (i.e. a credit or debit), using a UNION query to bring both sets of data into a single table works well here. After this, I compute a rolling sum using the difference between credit and debit, for each record, for each ID partition group. The ordering is chosen such that credits appear at the top of each partition while debits appear on the bottom.

Upvotes: 1

Related Questions