Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Pinto
Aged Yak Warrior
590 Posts |
 Posted - 2009-04-23 : 08:46:59
|
| I have two tables one to many relationship. There is a field TotalDue in the one table, TableA. In the many table, TableB there is a field AmtPaid. I want to sum AmtPaid and take it from TotalDue to give a BalanceLeft amount to display in a label on a windows form. I also want to pass a parameter which is the Id of the person in TableA and which links it to TableB. How can I do this in a stored procedure ?TIA |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-04-23 : 09:15:19
|
| SELECT (TotalDue-(SELECT TOP 1 SUM(AmtPaid) FROM TableB WHERE TableB.PersonID = TableA.PersonID GROUP BY PersonID)) As BalanceLeft FROM TableA WHERE PersonId = @Id; |
 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-04-23 : 09:27:41
|
| declare @t table (pid int, totaldue int)insert @tselect 1,1000 union allselect 2,500declare @r table (pid int, amtdue int)insert @rselect 1,100 union allselect 1,500 union allselect 2,100 union allselect 2,300 select t.totaldue - r.totalduefrom @t t join(select pid,sum(amtdue) as totaldue from @r group by pid) r on t.pid = r.pid |
|
|
|
Pinto
Aged Yak Warrior
590 Posts |
Posted - 2009-04-23 : 10:08:05
|
| select 1,100 union allselect 1,500 union allselect 2,100 union allselect 2,300what is the the 100 500 300 etc ? |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-04-23 : 10:16:42
|
| thats just an example...the 100,200 are the amt dues for each of the person ids.I just created a table declaration to illustrate.The SELECT statement is all you should worry about. |
|
|
|
|
|
|
|
|
Sum and subtract fields in two tables