| Author |
 Topic  |
|
SCHEMA
Posting Yak Master
192 Posts |
 Posted - 11/08/2012 : 14:19:52
|
I have this table which has companyname,Program and ReceivedAmount and other table where ProgramName,Accountnumber and AccountAmount is stored. So program will use money from this table based on account number.If money in one account is not enough it will go to next account for money.
CompanyName Program ActualReceivedAmount
A P1 140000
B P2 136000
C P2 76000
D P2 220000
E P2 52000
Program AccountNumber AccountAmount
P1 100011 24000
P1 100012 116000
P2 100045 180000
P2 100046 304000
I want to get output like this.
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
Here in output, Company A and Company D have 2 entries as they are splitted for 2 account number.
Please help
Thanks in advance. |
Edited by - SCHEMA on 11/08/2012 15:42:09
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/08/2012 : 21:01:26
|
| Any Help. |
 |
|
|
samskolli
Starting Member
2 Posts |
Posted - 11/08/2012 : 21:39:15
|
| If you use up all your money in program table for a company, lets say C, is that money in the program table still available for the next company? |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/08/2012 : 21:47:09
|
No. Second table will say for which program how many accountnumber are setup and how much money is allocated for each account.
what i actually need is how i can distribute money between 2 account for organization which is participating in the program. |
|
|
|
samskolli
Starting Member
2 Posts |
Posted - 11/09/2012 : 00:04:42
|
I understand your requirement now.
I can think of a rbar approach on top of my head. I will try to see tommorow morning if there could be a set based approach. |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/09/2012 : 07:24:27
|
| Thanks |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/09/2012 : 10:14:28
|
| Any help guys!!! |
|
|
|
Ifor
Constraint Violating Yak Guru
475 Posts |
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/09/2012 : 15:44:25
|
| Is it possible to do this in SQL. Please help |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 11/09/2012 : 16:02:07
|
I would have liked to help, but after reading your post 3 or four times, I couldn't follow what you are trying to do - I admit that it is my failing because others have understood what you are trying to do, but I suspect there may be others on the forum who are in a similar situation as I am.
You refer to "this table" and "other table". If you can name the tables - call them Table A and Table B if you like - that would make it a little clearer Also, I did not follow which table acts as the source and which is the destination, or what the rules are. Can you describe what your logic would be if you were to do this manually, one step at a time?
Also, if you want to post code or prserve white spaces, use the [code] and [/code] tags and put the text in between those two tags. |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/09/2012 : 16:08:34
|
Sure Sunita. Thank you.
I have Company table which has companyname,Program and ReceivedAmount and ProgramAccount table where ProgramName,Accountnumber and AccountAmount is stored. So program will use money from this table based on account number.If money in one account is not enough it will go to next account for money.
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
Here in output, Company A and Company D have 2 entries as they are splitted for 2 account number.
|
Edited by - SCHEMA on 11/09/2012 16:09:06 |
|
|
|
Ifor
Constraint Violating Yak Guru
475 Posts |
Posted - 11/09/2012 : 17:07:59
|
You have not explained why company B with program P2 uses account 100046 etc.
The only reason I can think for this is to get the best fit. ie It is the bin packing problem.
Read the articles I referenced to get an idea of how to do this in SQL.
Here is some background information from wikipedia:
http://en.wikipedia.org/wiki/Bin_packing_problem
|
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/09/2012 : 17:34:25
|
Because Program 2 has all money to support for
B,C, 40000 for D and E.
Company A is using P1 Accountnumber 100011(24000) and 100012(116000) which totals to 140000. Out of 220000 money received by D,it is first all money from 100045(180000) and remaining 40000 from 100046. Hope that is clear. |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/09/2012 : 22:08:30
|
| Where is Visakh,Peso,Khtan and Jsmith nowdays ??? Gurus!!! |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 11/10/2012 : 02:58:25
|
How can we know the distribution between accounts and across companies sharing the same program? Can't this answer be correct as well? The totals for program still add up as do the totals for company - just a different distribution.
CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 10
B P2 100046 135990
C P2 100046 76000
D P2 100045 179990
D P2 100046 40010
E P2 100046 52000
Be One with the Optimizer
TG |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/10/2012 : 10:00:07
|
Yes but we will not split organization into 2 account if the account itself has money. We will split only when money is not sufficient in the account.
For Example : For Program 2, there are 2 accounts 100045,100046).
Account(100045) has only 180000 so I have splitted Organization D into 2 Accountnumber. Organization D is receiving 220000.
D 100045 180000
D 100046 40000
If you add up this one below it will equal to total money for account 100046 which is 304000.
B P2 100046 136000
C P2 100046 76000
D P2 100046 40000
E P2 100046 52000
|
|
|
|
Ifor
Constraint Violating Yak Guru
475 Posts |
Posted - 11/10/2012 : 10:01:39
|
quote:
Originally posted by TG
How can we know the distribution between accounts and across companies sharing the same program? Can't this answer be correct as well? The totals for program still add up as do the totals for company - just a different distribution.
CompanyName Program AccountNumber ActualReceivedAmount
A P1 100011 24000
A P1 100012 116000
B P2 100045 10
B P2 100046 135990
C P2 100046 76000
D P2 100045 179990
D P2 100046 40010
E P2 100046 52000
Be One with the Optimizer
TG
Exactly the point I was trying to get over to the OP.
The only reason, I can see, for going with his results is that it is a better fit.
|
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/10/2012 : 10:06:01
|
Ifor,
We will not try to split unless it is required. we will try to use as money as possible in that account. |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/10/2012 : 10:10:29
|
It can be this combination also
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 |
Edited by - SCHEMA on 11/10/2012 10:16:07 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5467 Posts |
Posted - 11/10/2012 : 12:29:16
|
Because there is not necessarily a single right answer will any right answer do? Or is there some preference like evenly distribute an account across all companies in a program? Or perhaps apply some sequence to the companies so that the first company gets all available then move to the next company, etc.?
Be One with the Optimizer
TG |
|
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 11/10/2012 : 15:21:48
|
This will work
apply some sequence to the companies so that the first company gets all available then move to the next company
Thank you TG. |
|
|
|
Topic |
|
How to do this complex logic!!!!!!
