How can I build my SQL query from these tables?

Posted by vee on Stack Overflow See other posts from Stack Overflow or by vee
Published on 2011-01-13T14:05:51Z Indexed on 2011/01/13 14:53 UTC
Read the original article Hit count: 303

Filed under:
|
|

Hi All,

I'm thinking of building query from these 2 tables (on SQL Server 2008). I have 2 tables as shown below:

Table 1

 MemberId  . MemberName .  Percentage .  Amount1
 00000001    AAA                 1.0     100
 00000002    BBB                 1.2     800
 00000003    ZZZ                 1.0     700

Table 2

 MemberId  . MemberName .  Percentage .  Amount2
 00000002    BBB                 1.5     500
 00000002    BBB                 1.6     100
 00000002    BBB                 1.6     150

The result I want is

 MemberId  . MemberName .  Percentage .  Amount . NettAmount
 00000001    AAA                 1.0      100     100
 00000002    BBB                 1.2      800      50 <-- 800-(500+100+150)
 00000002    BBB                 1.5      500     500
 00000002    BBB                 1.6      650     650
 00000003    ZZZ                 1.0      700     700

50 comes from 800 in Table1 minus sum of Amount2 in table2 for MemberID=00000002

Plz someone help me to build the query to reach this result.

Thank you in advance.

© Stack Overflow or respective owner

Related posts about sql

Related posts about tsql