SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Complex Query		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SCHEMA
Posting Yak Master

192 Posts
Posted - 11/12/2012 :  09:40:17  Show Profile  Reply with Quote 
I have Company table which has companyname,Program and ReceivedAmount

Program Account Table is like Lookup Table.It says for which program and for acccount number how much money is allocated. From this table money is distributed based on Program and Accountnumber.

Company ( Main table)

CompanyName Program ActualReceivedAmount
A P1 140000
B P2 136000
C P2 76000
D P2 220000
E P2 52000

ProgramAccount ( This is lookup table)

Program AccountNumber AccountAmount
P1 100011 24000
P1 100012 116000
P2 100045 180000
P2 100046 304000

I want to get output like this from above.

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100046 136000
C P2 100046 76000
D P2 100045 180000
D P2 100046 40000
E P2 100046 52000

Or

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 136000
C P2 100045 44000
C P2 100046 32000
D P2 100046 220000
E P2 100046 52000

Or this combination

CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 136000
C P2 100046 76000
D P2 100046 220000
E P2 100046 8000
E P2 100045 44000

Any Combination is fine. We will not try to split unless it is required. we will try to use as money as possible in that account.We will not try to split unless it is required. 
we will try to use as money as possible in that account.


Please help.

robvolk
Most Valuable Yak

USA
15559 Posts
Posted - 11/12/2012 :  10:05:52  Show Profile  Visit robvolk's Homepage  Reply with Quote
Is this a new question, or same as this one: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=180498

Please don't start new threads for an existing question.
Go to Top of Page

SCHEMA
Posting Yak Master

192 Posts
Posted - 11/12/2012 :  10:26:13  Show Profile  Reply with Quote
Robvolk,

This is same question but i have made it simpler than before so it is easy to understand. It was confusing before so I created new one.
Please remove the old one.

Thanks and I have been trying to break my head but couldn't come up with solution :(
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000