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: 306
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