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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Complex Query

Author  Topic 

SCHEMA
Posting Yak Master

192 Posts

Posted - 2012-11-12 : 09:40:17
[code]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.[/code]
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

[code]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.[/code]

Please help.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-11-12 : 10:05:52
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 - 2012-11-12 : 10:26:13
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
   

- Advertisement -