Author |
Topic |
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2012-11-08 : 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 ActualReceivedAmountA P1 140000B P2 136000C P2 76000D P2 220000E P2 52000 Program AccountNumber AccountAmountP1 100011 24000P1 100012 116000P2 100045 180000P2 100046 304000I want to get output like this.CompanyName Program AccountNumber ActualReceivedAmountA P1 100011 24000A P1 100012 116000B P2 100046 136000C P2 100046 76000D P2 100045 180000D P2 100046 40000E P2 100046 52000Here in output, Company A and Company D have 2 entries as they are splitted for 2 account number.Please helpThanks in advance. |
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2012-11-08 : 21:01:26
|
Any Help. |
|
|
samskolli
Starting Member
2 Posts |
Posted - 2012-11-08 : 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 - 2012-11-08 : 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 - 2012-11-09 : 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 - 2012-11-09 : 07:24:27
|
Thanks |
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2012-11-09 : 10:14:28
|
Any help guys!!! |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2012-11-09 : 12:58:10
|
This looks like a variation on the 'bin packing' problem.There are some articles on this, by Hugo Kornelis, here:http://sqlblog.com/blogs/hugo_kornelis/archive/tags/Bin+Packing/default.aspx |
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2012-11-09 : 15:44:25
|
Is it possible to do this in SQL. Please help |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-11-09 : 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 - 2012-11-09 : 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 ActualReceivedAmountA P1 140000B P2 136000C P2 76000D P2 220000E P2 52000ProgramAccount ( This is lookup table)Program AccountNumber AccountAmountP1 100011 24000P1 100012 116000P2 100045 180000P2 100046 304000I want to get output like this from above.CompanyName Program AccountNumber ActualReceivedAmountA P1 100011 24000A P1 100012 116000B P2 100046 136000C P2 100046 76000D P2 100045 180000D P2 100046 40000E P2 100046 52000Here in output, Company A and Company D have 2 entries as they are splitted for 2 account number. |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2012-11-09 : 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 - 2012-11-09 : 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 - 2012-11-09 : 22:08:30
|
Where is Visakh,Peso,Khtan and Jsmith nowdays ??? Gurus!!! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-10 : 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 ActualReceivedAmountA P1 100011 24000A P1 100012 116000B P2 100045 10B P2 100046 135990C P2 100046 76000D P2 100045 179990D P2 100046 40010E P2 100046 52000Be One with the OptimizerTG |
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2012-11-10 : 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 180000D 100046 40000If you add up this one below it will equal to total money for account 100046 which is 304000.B P2 100046 136000C P2 100046 76000D P2 100046 40000E P2 100046 52000 |
|
|
Ifor
Aged Yak Warrior
700 Posts |
Posted - 2012-11-10 : 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 ActualReceivedAmountA P1 100011 24000A P1 100012 116000B P2 100045 10B P2 100046 135990C P2 100046 76000D P2 100045 179990D P2 100046 40010E P2 100046 52000Be One with the OptimizerTG
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 - 2012-11-10 : 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 - 2012-11-10 : 10:10:29
|
It can be this combination alsoCompanyName Program AccountNumber ActualReceivedAmountA P1 100011 24000A P1 100012 116000B P2 100045 136000C P2 100045 44000C P2 100046 32000D P2 100046 220000E P2 100046 52000Or this combinationCompanyName Program AccountNumber ActualReceivedAmountA P1 100011 24000A P1 100012 116000B P2 100045 136000C P2 100046 76000D P2 100046 220000E P2 100046 8000E P2 100045 44000 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2012-11-10 : 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 OptimizerTG |
|
|
SCHEMA
Posting Yak Master
192 Posts |
Posted - 2012-11-10 : 15:21:48
|
This will workapply some sequence to the companies so that the first company gets all available then move to the next company Thank you TG. |
|
|
Previous Page&nsp;
Next Page
|